Compile an Excel VBA Script to modify a connection property in another workbook
I have a workbook that contains a macro that i wish to use to update the location of a connection in another workbook. The VBA script creates a folder, populates it with a log file containing data called log.txt and a copy of an excel file that is pre formatted to fill with the data allowing the user to see graphs and a detailed breakdown of the data. it is a door opening log, tracking numbers of times the door has been used.

here is the VBA code I've come up with so far.
note: I did a couple of years programming in C++ but haven't touched it in a decade. I have tried searching around for the code and even recording a macro of the actions I take when refreshing the connection manually. however if I try and use that code it gives a "Run time error 1004" Application defined or object defined error.

Here is the code. The commented out bit at the bottom is the result of the macro recorded from manually altering the connection.

Any help would be greatly received.



    Sub Lof_File_Macro()
    
    '
    ' Log_file_Macro Macro
    ' Runs script for monthly counts
    '
    Dim strfolder1, strmonthno, strmonth, stryear, strfoldername, strfile, strmonyr, stlogfile, strfutfile
    
    'date strings defined using date functions - ofset for 28 days to allow running anytime within 20 days into the next month whilereturning correct month
    strmonthno = Month(Date - 28)
    strmonth = MonthName((strmonthno), True)
    stryear = Year(Date - 28)
    
    
    strmonyr = " " & strmonth & " " & stryear
    strfolder = "C:\Users\jtaylor7\Desktop\futures\People Counter" & strmonyr
    strfile = "Futures People" & strmonyr & ".xls"
    
    
    strlogfile = strfolder & "\" & "log" & strmonyr & ".txt"
    strfutfile = strfolder & "\" & strfile
    
    
    MkDir (strfolder)
    
    
    FileCopy "C:\Users\jtaylor7\Desktop\futures\log.log", strlogfile
    FileCopy "C:\Users\jtaylor7\Desktop\futures\template.xls", strfutfile
    
    
    'Workbooks.Open Filename:=strfutfile
    'ActiveWorkbook.Connections.AddFromFile (strlogfile)
    '
    '
     
    ' Perform data connection modification on file
    
    
    ''    Windows(strfutfile).Activate
    '    With ActiveWorkbook.Connections("log")
    '        .Name = "log"
    '        .Description = ""
    '  End With
    '    Range("$A$1:$H$1").Select
    'With Selection.QueryTable
    '        .Connection = "TEXT;strlogfile"
    '        .TextFilePlatform = 850
    '        .TextFileStartRow = 1
    '        .TextFileParseType = xlDelimited
    '        .TextFileTextQualifier = xlTextQualifierDoubleQuote
    '        .TextFileConsecutiveDelimiter = False
    '        .TextFileTabDelimiter = False
    '        .TextFileSemicolonDelimiter = False
    '        .TextFileCommaDelimiter = True
    '        .TextFileSpaceDelimiter = False
    '        .TextFileOtherDelimiter = "/"
    '        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    '        .TextFileTrailingMinusNumbers = True
    '        .Refresh BackgroundQuery:=False
    '    End With
    '    Range("I4").Select
    '    ActiveWorkbook.Connections("log").Refresh
    ''    Windows("Run Me.xls").Activate
    
    '
    End Sub

I know its a bit messy, and if anyone needs any further data please ask.
Compile an Excel VBA Script to modify a connection property in another workbook
===

I have a workbook that contains a macro that i wish to use to update the location of a connection in another workbook. The VBA script creates a folder, populates it with a log file containing data called log.txt and a copy of an excel file that is pre formatted to fill with the data allowing the user to see graphs and a detailed breakdown of the data. it is a door opening log, tracking numbers of times the door has been used.

here is the VBA code I've come up with so far.
note: I did a couple of years programming in C++ but haven't touched it in a decade. I have tried searching around for the code and even recording a macro of the actions I take when refreshing the connection manually. however if I try and use that code it gives a "Run time error 1004" Application defined or object defined error.

Here is the code. The commented out bit at the bottom is the result of the macro recorded from manually altering the connection.

Any help would be greatly received.



    Sub Lof_File_Macro()
    
    '
    ' Log_file_Macro Macro
    ' Runs script for monthly counts
    '
    Dim strfolder1, strmonthno, strmonth, stryear, strfoldername, strfile, strmonyr, stlogfile, strfutfile
    
    'date strings defined using date functions - ofset for 28 days to allow running anytime within 20 days into the next month whilereturning correct month
    strmonthno = Month(Date - 28)
    strmonth = MonthName((strmonthno), True)
    stryear = Year(Date - 28)
    
    
    strmonyr = " " & strmonth & " " & stryear
    strfolder = "C:\Users\jtaylor7\Desktop\futures\People Counter" & strmonyr
    strfile = "Futures People" & strmonyr & ".xls"
    
    
    strlogfile = strfolder & "\" & "log" & strmonyr & ".txt"
    strfutfile = strfolder & "\" & strfile
    
    
    MkDir (strfolder)
    
    
    FileCopy "C:\Users\jtaylor7\Desktop\futures\log.log", strlogfile
    FileCopy "C:\Users\jtaylor7\Desktop\futures\template.xls", strfutfile
    
    
    'Workbooks.Open Filename:=strfutfile
    'ActiveWorkbook.Connections.AddFromFile (strlogfile)
    '
    '
     
    ' Perform data connection modification on file
    
    
    ''    Windows(strfutfile).Activate
    '    With ActiveWorkbook.Connections("log")
    '        .Name = "log"
    '        .Description = ""
    '  End With
    '    Range("$A$1:$H$1").Select
    'With Selection.QueryTable
    '        .Connection = "TEXT;strlogfile"
    '        .TextFilePlatform = 850
    '        .TextFileStartRow = 1
    '        .TextFileParseType = xlDelimited
    '        .TextFileTextQualifier = xlTextQualifierDoubleQuote
    '        .TextFileConsecutiveDelimiter = False
    '        .TextFileTabDelimiter = False
    '        .TextFileSemicolonDelimiter = False
    '        .TextFileCommaDelimiter = True
    '        .TextFileSpaceDelimiter = False
    '        .TextFileOtherDelimiter = "/"
    '        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    '        .TextFileTrailingMinusNumbers = True
    '        .Refresh BackgroundQuery:=False
    '    End With
    '    Range("I4").Select
    '    ActiveWorkbook.Connections("log").Refresh
    ''    Windows("Run Me.xls").Activate
    
    '
    End Sub

I know its a bit messy, and if anyone needs any further data please ask.