macro run in multiple locations but uses the same file in the multiple locations ie the macro needs to select the file in same folder its in

[A Nonomus]

how do I add a Macro that will work with multiple CSV files of the same name. but only search the one thats in the same(file) location. as the spreadsheet using the macro

normally you could use .\ to reference the same folder, but this doesnt work.
any help would be great


My spreadsheet Macro will be copied to sits in multiple location i folders with months of the year. ( July, AUG, Sept.)
CSV is DATA.csv that also exists in (july, AUG, Sept.)
so when the macro runs in July folder it will use folder = "C:\July\AutodeskOutputLog.csv" but when I copy to Aug the file will be folder = "C:\AUG\AutodeskOutputLog.csv"

hope thats been explained clearly enough.
hope you have a great day

 

[Jon von der Heyden]

Hi

I think I undestand you. The file with the macro will be callable as ThisWorkbook. To get the directory path of ThisWorkbook you would use ThisWorkbook.Path. It will return the path without the path separator at the end, so you need to concatenate that back on, e.g. ThisWorkbook.Path & "\" or ThisWorkbook.Path & Application.PathSeparator.

 

[A Nonomus]

hey Jon, thanks for such a speedy response, sorry bit of a noobie so hope this helps. it runs fine if I specify the the folder location (ie"C:\OutputLog.csv")
I want it to look for a file called OutputLog.csv in same location as spreadsheet (Blank.XLSM)

thanks again
KC

"Sub LoadFromFile()
Dim fileName As String, folder As String

folder = "C:\OutputLog.csv"
fileName = ActiveCell.Value
Range("A1").Select
ActiveCell.Offset(0, 0).Range("A1").Select

With ActiveSheet.QueryTables _
.Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
.FieldNames = True
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub"

 

[Jon von der Heyden]

Hi

I think that you want this line:

folder = ThisWorkbook.Path & "\OutputLog.csv"

 

[A Nonomus]

Works a treat but but for some reason the file doesn't update when opened ( i think it might be related to Data in memory.) tried clearing Folder = and filename= at end of script. But I created a button to assign the macro too.) and that works well too


Thanks Jon (legend) Von der Heyden