I have a demo.xlsm file with macros running automatically when I open the workbook but it needs to read a .csv file for data and a Lamda_Logo.png file. The Lamda_Logo.png has always the same name. The name of the .csv file is unknown but it will have the same path with my demo.xlsm

How can I use variables in VBA code to reference the path and name of my csv file?

I have tried

csvPath = Dir(Thisworkbook.Path & "\*.csv") 

but it doesnt work. The only command that works is

ThisWorkbook.Path & "\Lamda_Logo.png"

to load the picture but I cant do the same for the .csv file since I won't know the name..

Here is my code:

Private Sub Workbook_Open()


Dim ws As Worksheet
Dim imagePath As String
Dim imgLeft As Double
Dim imgTop As Double

Set ws = ActiveSheet
imagePath = ThisWorkbook.Path & "\Lamda_Logo.png"
imgLeft = ActiveCell.Left
imgTop = ActiveCell.Top

'Width & Height = -1 means keep original size
ws.Shapes.AddPicture _
    fileName:=imagePath, _
    LinkToFile:=msoFalse, _
    SaveWithDocument:=msoTrue, _
    Left:=imgLeft, _
    Top:=imgTop, _
    Width:=-1, _
    Height:=-1
    
     Dim fileName As String, folder As String
    
    folder = ThisWorkbook.Path & "\"
    fileName = "#Lamda_Dev_Projects_Risk_Register_191020211650.csv"
    
    ActiveCell.Offset(1, 0).Range("A12").Select
    
With Worksheets("Sheet1").Range("A13:T13")
 .Font.Size = 12
End With

Worksheets("Sheet1").Range("A13:T13").Font.Bold = True

Range("A13:T13").Interior.Color = RGB(147, 175, 186)
        
    With ActiveSheet.QueryTables _
        .Add(Connection:="TEXT;" & folder & fileName, Destination:=ActiveCell)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .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 = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=True
    End With
    
    'check for filter, turn on if none exists
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A13").AutoFilter
  End If
    

End Sub

CodePudding user response：

The Dir function will return the File Name, but without the Path. So, if your workbook is "D:\Files\Testing\Workbook1.xlsx", and your CSV is "D:\Files\Testing\Data_20211020.csv", then:

• ThisWorkbook.Path will be "D:\Files\Testing"
• Dir(ThisWorkbook.Path & "\*.csv") will be "Data_20211020.csv"
• The result you want for csvPath is "D:\Files\Testing\Data_20211020.csv"

As such, the full path will be

csvPath = ThisWorkbook.Path & "\" & Dir(ThisWorkbook.Path & "\*.csv") 

(Remember that Dir will return a pseudo-random file that matches the mask/filter, and calling Dir() again will return another one, until they have all been returned. If you have multiple CSV files, you might need a more specific filter, such as Dir(ThisWorkbook.Path & "\Data_*_Processed.csv") or something)