PDA

View Full Version : Filename Variable



farmdwg
03-15-2017, 08:54 AM
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
03-15-2017, 09:36 AM
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
03-15-2017, 09:44 AM
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
03-15-2017, 09:55 AM
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
03-15-2017, 10:27 AM
Many thanks... the first section works like a charm.

farmdwg
03-15-2017, 11:07 AM
Actually it only works on the tab that is currently selected. Overwrote data on another sheet.

mdmackillop
03-15-2017, 11:17 AM
Without MakeLink code we can't advise.

farmdwg
03-15-2017, 01:14 PM
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
03-15-2017, 02:14 PM
This looks intended to import data from 3 csv files to 3 worksheets when the workbook is opened. What is the issue?