Filename Variable

[farmdwg]

I wanted to see if this is at all possible with the way I'm importing files into my excel sheet.

I've got VBA code that allows me to import the file from the relative directory.

VBA Code I'm using:

MakeLink ThisWorkbook.Path & "\" & "msg_adv.csv", "MessageSize", "MessageSize!A1", 1, Array(5), "m/d/yyyy"

File name could look like:

• msg_adv.csv
• customer_msg_adv.csv
• customermsg_adv.csv

My question is that sometimes the file I want to import has a prefix. Is there a way to script for the possibility of a prefix?

Thanks!

[mdmackillop]

You could run a dir command to test for any prefix and pass the result to your code

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

[farmdwg]

So I'm a bit of a VBA noob so I was just hoping I could simply modify my import command to include:

MakeLink ThisWorkbook.Path & "\" & "\*disp_cluster_overview.csv", "test", "test!A1", 1, Array(2), "@"

[mdmackillop]

If you have more than one variation in the folder then the first function can be modified to loop through all results.
If you will have only one then either should work.

x = Dir(ThisWorkbook.Path & "\*msg_adv.csv")
MakeLink ThisWorkbook.Path & "\" & x, "MessageSize", "MessageSize!A1", 1, Array(5), "m/d/yyyy"
'or
MakeLink ThisWorkbook.Path & "\" & Dir(ThisWorkbook.Path & "\*msg_adv.csv"), "MessageSize", "MessageSize!A1", 1, Array(5), "m/d/yyyy"

[farmdwg]

Many thanks... the first section works like a charm.

[farmdwg]

Actually it only works on the tab that is currently selected. Overwrote data on another sheet.

[mdmackillop]

Without MakeLink code we can't advise.

[farmdwg]

Private Sub Workbook_Open()    CSVImport
End Sub
Sub CSVImport()
    On Error Resume Next
    For Each c In ThisWorkbook.Connections
        c.Delete
    Next
	
    MakeLink ThisWorkbook.Path & "\" & "top_sites.csv", "TopSites", "TopSites!D1", 1, Array(2), "@"
    MakeLink ThisWorkbook.Path & "\" & "sites_by_months.csv", "SitesMonths", "SitesMonths!H17", 1, Array(1, 1, 9), "m/d/yyyy"
	x = Dir(ThisWorkbook.Path & "\*msg_adv.csv") 
	MakeLink ThisWorkbook.Path & "\" & x, "Test", "Test!A1", 1, Array(5), "m/d/yyyy" 
	
	End Sub


	Sub MakeLink(strFileName As String, strSheetName As String, strRangeAddress As String, startRow As Long, FirstColmcsvFormat, FirstColmSheetFormat)
    With Sheets(strSheetName).QueryTables.Add(Connection:="TEXT;" & strFileName, Destination:=Range(strRangeAddress))
         '.Name = "ABC" 'no need to name every query the same (unless you're going to use them)!
         '.FieldNames = True'default
         '.RowNumbers = False 'default
         '.FillAdjacentFormulas = False 'default
         '.PreserveFormatting = True 'default
         '.RefreshOnFileOpen = False 'default
        .RefreshStyle = xlOverwriteCells
         '.SavePassword = False 'default
         '.SaveData = True 'default
         '.AdjustColumnWidth = True 'default
         '.RefreshPeriod = 0 'default
         '.TextFilePromptOnRefresh = False 'default
        .TextFilePlatform = 437
        .TextFileStartRow = startRow
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
         '.TextFileColumnDataTypes = Array(2)
         '.TextFileColumnDataTypes = Array(5)
        .TextFileColumnDataTypes = FirstColmcsvFormat
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        .ResultRange.Columns(1).NumberFormat = FirstColmSheetFormat
         '.ResultRange.Columns(1).NumberFormat = "m/d/yyyy" 'or however you want it.
    End With
End Sub

[mdmackillop]

This looks intended to import data from 3 csv files to 3 worksheets when the workbook is opened. What is the issue?