Excel VBA SQL Query Filename
 

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(Arra y( _
"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

Excel VBA SQL Query Filename
 

I think the answer is simple. The tilda is a special character and you
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?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158796

Microsoft Office Help

Excel VBA SQL Query Filename
 

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.

Excel VBA SQL Query Filename
 

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.

Excel VBA SQL Query Filename
 

the tilda character microsoft started in window 95 using in a file name
so the DOS commands could work in the windows environment. Filenames
and folders were given a short 8 character name and a window name so the
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 around
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 & "ORDER
BY " & _
chr(39) & FileName & ".product" & chr(39) & ", " & _
chr(39) & FileName & ".purpose" & chr(39) & ", " & _
chr(39) & FileName & ".group" & chr(39)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158796

Microsoft Office Help

Excel VBA SQL Query Filename
 

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

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).