使用配置文件自动执行 SQL 查询

Question

我正在尝试生成每周报告列表，每个报告都以文件夹的形式存在，其中包含使用 SQL 查询的多个 excel 文件。有没有办法让某种“配置”文件输入 SQL 服务器地址和端口、日期和其他相关数据，这些数据在文件夹中的相同文件之间是恒定的 - 但不一定在不同文件夹的文件之间是恒定的 - 而无需手动更改每个文件？

Answer 1

这有帮助吗？

在同一个文件夹中，我放置了一个名为“config.txt”的文件以及运行此宏的工作簿。

配置文件内容：

您可以使用此宏从中提取信息：

Sub readConfig()
    Dim configPath As String
    
    '"build a path for a file named "config.txt" located in the same folder as current workbook"
    configPath = Application.ActiveWorkbook.path & Application.PathSeparator & "config.txt"
    
    '"attempts to open file above, if config file is not found, go to error handling"
    On Error GoTo fileErrorHandler
        Open configPath For Input As #1
    On Error GoTo 0
    
    Dim serverName As String, login As String, password As String
    
    While Not EOF(1)
        Line Input #1, configLine
        Dim flag As String, value As String
        
        '"split line of config file to flag and value, assign value to a variable based on it's flag, if the split fails, go to error handling"
        On Error GoTo splitErrorHandler
            flag = Split(configLine, "=")(0)
            value = Split(configLine, "=")(1)
        On Error GoTo 0
        Select Case flag
            Case "server_name"
                serverName = value
            Case "login"
                login = value
            Case "password"
                password = value
        End Select
    Wend
    Close #1
    
    Debug.Print "server name is: " & serverName
    Debug.Print "login is name is: " & login
    Debug.Print "password is: " & password
    
    Exit Sub
fileErrorHandler:
    MsgBox "config file does not exist"
    Exit Sub
splitErrorHandler:
    Close #1
    MsgBox "unexpected string in config file"
    Exit Sub
End Sub

输出：