VBA: how to get variables into querry code

[Hollands Nieuwe]

Hello,

I need to import external data in excel daily.
The path and the file change several times.

I already made 2 variables for path and filename.
How do I get them in the querry code in VBA.

see belows part of the total code I use for the script.
these are the variables I want to use. for example I gave them here
the values also

strNamePath = \\PC_TRADE\Documents\Logs\
strNameFile = Unpaid_20111129.txt
strPathFile = \\PC_VAN_TRADERS6\Documents\Adyen Logs
\Unpaid_20111129.txt


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\PC_TRADE\Documents\Logs\Unpaid_20111129.txt",
Destination _
:=Range("A3"))
.Name = "Unpaid_20111129"
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 2, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


thnx in advance.

Maurice

 

[Don Guillett]

You can break up your string and continue
string" & variable & " string " & variable & " etc

 

[Michael Bednarek]

Hello,

I need to import external data in excel daily.
The path and the file change several times.

I already made 2 variables for path and filename.
How do I get them in the querry code in VBA.

see belows part of the total code I use for the script.
these are the variables I want to use. for example I gave them here
the values also

strNamePath = \\PC_TRADE\Documents\Logs\
strNameFile = Unpaid_20111129.txt
strPathFile = \\PC_VAN_TRADERS6\Documents\Adyen Logs
\Unpaid_20111129.txt


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\PC_TRADE\Documents\Logs\Unpaid_20111129.txt",
Destination _
:=Range("A3"))
.Name = "Unpaid_20111129"

[snip]
Try:
"TEXT;" & strnamePath & strNameFile,
...
.Name = Left(strNameFile, Len(strNameFile) - 4)
------------------------------------------------------------
To obtain strNamePath from the user, try:
Function GetFldr() As String
'Display a dialogue to pick a directory

With Application.FileDialog(msoFileDialogFolderPicker)
.ButtonName = "Use"
.InitialFileName = "\\PC_TRADE\Documents\Logs\"
.InitialView = msoFileDialogViewDetails
.Title = "Pick a directory"
If .Show = -1 Then
GetFldr = .SelectedItems(1)
Else
MsgBox "You did not pick a directory.", vbExclamation + vbOKOnly, "Testing GetFldr"
GetFldr = ""
End If
End With
Set myDlg = Nothing
End Function

and in the main sub:
strNamePath = GetFldr()
------------------------------------------------------------
To obtain strNameFile from the user, try
Function GetFilename(strNamePath As String) As String

With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = strNamePath & "Unpaid_*.txt"
.InitialView = msoFileDialogViewDetails
.Title = "Select file"
If .Show = -1 Then
GetFilename = .SelectedItems(1)
Else
MsgBox "You did not press the Open button.", vbInformation + vbOKOnly
GetFilename = ""
End If
End With
End Function

and in the main sub:
strNameFile = GetFilename(strNamePath)

Both untested.

The returned value for strNameFile will be a fully qualified filename.
To break that filename into components (drive, path, name, extension), use
Scripting.FileSystemObject and its Get* methods.