Excel VBA SQL Query Filename

[Jaq23]

I recorded a macro to pull in an external dbf file using a SQL query
in VBA (Excel 2003 on an XP machine).

Everything works great until I point it at the original file (was
pointing at a renamed copy) that I want to pull in. The files I need
to pull in will have the format <output filename>~<model 1>~<model
2>~<sub model>.dbf.

The SQL query doesn't like the strange characters (~'s) in the
filename and if I try a wildcard within the filename (i.e. <output
filename>*<sub model> or <output filename>%<sub model>) it doesn't
like that either.

I can't create copies of the output files every time I want to pull
one in (some of them are huge ~1.4GB) and I can't rename the original
file as they're used by other people and processes.
Any suggestions on how I can get this to work consistently without
renaming or copying files?

Original code:

Sub ImportCNP()
Dim OutputLoc As String
Dim SQLQuery As String
Dim SQLQuery2 As String
Dim temp
Dim ModelLoc As String
Dim FileName As String

OutputLoc = "cnp_start"
ModelLoc = Range("cnp_loc").Value
FileName = Range("cnp_source").Value
FileName = Replace(FileName, ".dbf", "")

Range(Range(OutputLoc), Range(OutputLoc).End
(xlDown)).EntireRow.ClearContents
SQLQuery = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM " &
FileName & " " & FileName & "" & Chr(13) & "" & Chr(10) & "WHERE " &
FileName & ".time = ' 0'" & Chr(13) & "" & Chr(10) & "ORDER BY
" & FileName & ".product, " & FileName & ".purpose, " & FileName &
".group"

Sheets("cnp_test").Select
Range("cnp_start").Select
Call getDBF(SQLQuery, OutputLoc, ModelLoc)

End Sub

Sub getDBF(strQuery As String, OutLoc As String, ModelLoc As String)
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;Driver={Microsoft FoxPro VFP Driver
(*.dbf)};UID=;;SourceDB=" & ModelLoc &
";SourceType=DBF;Exclusive=No;BackgroundFetch" _
), Array("=Yes;Collate=Machine;Null=Yes;Deleted=Yes;")),
Destination:=Range(OutLoc))
.CommandText = Array(strQuery)
.Name = "Query from Model_" & OutLoc
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[joel]

I think the answer is simple. The tilda is a special character and yo
would need to replace a single tilda with two tildas

From
FileName = Range("cnp_source").Value
FileName = Replace(FileName, ".dbf", "")

To
FileName = Range("cnp_source").Value
FileName = Replace(FileName, ".dbf", "")
FileName = Replace(FileName, "~", "~~")

You can also simplify your SQL a little bit like this

From
SQLQuery = "SELECT *" & Chr(13) & "" & Chr(10) & "FROM " &
FileName & " " & FileName & "" & Chr(13) & "" & Chr(10) & "WHERE " &
FileName & ".time = ' 0'" & Chr(13) & "" & Chr(10) & "ORDER BY
" & FileName & ".product, " & FileName & ".purpose, " & FileName &
".group"

To
SQLQuery = "SELECT *" & vbcrlf & "FROM " &
FileName & " " & FileName & "" & vbcrlf & "WHERE " &
FileName & ".time = ' 0'" & vbcrlf & "ORDER BY
" & FileName & ".product, " & FileName & ".purpose, " & FileName &
".group"


Why do you have the File name twice on the 2nd line

 

[Jaq23]

It's probably there twice because it's based on a recorded macro, I
couldn't remember the syntax for the ODBC connections.

I'll put this in now and see how it goes although in the meantime I'm
going to try posing a further question if you don't mind as I suspect
this may become a bit of an issue once I've ammended my code:

The new filename is ridiculously long (around 60+ characters) which
causes issues when feeding the SQL query to the database. I've tried
working on breaking it down into an array but I'm really struggling to
not get a "General ODBC Error" when it gets to the Refresh step.

Thanks so much for your help so far. Fingers crossed.

 

[Jaq23]

Ok, I've tried that and when it gets to the CommandText line I get a
Type Mismatch Error.

If I remove the Array function and feed the whole string directly
through (at a length of 402), I get a SQL Syntax Error at the Refresh
line.

 

[joel]

the tilda character microsoft started in window 95 using in a file nam
so the DOS commands could work in the windows environment. Filename
and folders were given a short 8 character name and a window name so th
DOs commands would stilloperate. If a filename was


abcdefghijkl.xls

the full name was the window name and the DOs name would be
abcdef~1.xls

A second file was given the name

abcdefghijkm.xls
abcdef~2.xls


I started look at you SQL and think there are more changes required

From
FileName = Range("cnp_source").Value
FileName = Replace(FileName, ".dbf", "")

To
FileName = Range("cnp_source").Value
FileName = Replace(FileName, ".dbf", "")
FileName = chr(39) & FileName & chr(39) 'put single quotes aroun
filename



Or this

From
SQLQuery = "SELECT *" & vbcrlf & "FROM " &
FileName & " " & FileName & "" & vbcrlf & "WHERE " &
FileName & ".time = ' 0'" & vbcrlf & "ORDER BY
" & FileName & ".product, " & FileName & ".purpose, " & FileName &
".group"
to
SQLQuery = "SELECT *" & vbcrlf & "FROM " & _
chr(39) & FileName & chr(39) & " " & _
chr(39) & FileName & chr(39) & vbcrlf & "WHERE " & _
chr(39) & FileName & ".time" & chr(39) & "= ' 0'" & vbcrlf & "ORDE
BY " & _
chr(39) & FileName & ".product" & chr(39) & ", " & _
chr(39) & FileName & ".purpose" & chr(39) & ", " & _
chr(39) & FileName & ".group" & chr(39

 

[Jaq23]

Finally got it working. For those looking this up at a later date, the
main issues were:

1. Length of the SQL Query - reduced this by using a table alias
(SELECT * from longtablename as F ... ORDER by F.product ...) and the
length came down to 158.
2. Quotation marks around the filename. Once I put these in, and took
out the bit where I replaced "~" with "~~", the query recognised the
file easily enough. I left the filename exactly as it is shown in
Explorer with only the file extension .dbf removed from the end.

Note: The change to vbCrLf didn't affect the pulling in but it does
look much tidier.

Thank you so much for your help joel, you've saved me a whole extra
day of wasting time on this. And helped tidy the code all at once!

It now pulls the file in within a few seconds (alternative methods are
to manually export from FoxPro which wouldn't suit my users who are
oblivious to FoxPro or to use a specific add-in designed for the
output which takes half an hour - not kidding - to pull in one little
file).