PDA

View Full Version : Solved: Excel query from foxpro with variables



maddchad
01-06-2009, 11:42 AM
I hope someone can help me,

I am trying to create a macro that querys a foxpro database from a particular date the file name is CKmmddyy. The code works fine if I type in the value but if I use the variables, it gives me a syntax error on the line : .Refresh BackgroundQuery:=False

Thanks in advance for the help.




Sub DiscountQuery()
'
' Discount Query
' Macro recorded 1/2/2009 by Support7
'
'
Dim Month As String
Dim Day As String
Dim Year As String
Dim Filename As String
Dim Command As String
Dim Foldername As String

Sheets("DiscountQuery").Select
Range("a2,M3000").Select
Selection.Delete Shift:=xlUp

Month = Range("c1").Value
If Len(Month) = 1 Then
Month = "0" + Month
Else
End If

Day = Range("b1").Value
If Len(Day) = 1 Then
Day = "0" + Day
Else
End If

Year = Range("d1").Value
Year = Right(Year, 2)
Foldername = Range("a1").Value

Filename = Foldername + Month + Day + Year
Command = "Select * From " + Filename

Sheets("DiscountQuery").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=c:\DDWIN\Data\OR;SourceType=DBF;Exclusive=No;Back groundFetch=Yes;Collate=Machine;Nu" _
), Array("ll=Yes;Deleted=Yes;")), Destination:=Range("A1"))
.CommandText = Array(Command)
.Name = "+Connect to New Data Source"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


Edit Lucas: VBA Tags added to code. You can select your code when posting and hit the vba button to format your code for the forum.

Kenneth Hobs
01-06-2009, 12:25 PM
Welcome to the forum!

First off, be sure that you use the ampersand, &, rather than "+" when you want to do string concatenation.

maddchad
01-06-2009, 12:36 PM
Thanks,
I tried that and still no go.

maddchad
01-06-2009, 02:01 PM
I think it has to do with the quotation marks in this line :
.CommandText = Array('Select * From OR060209')
they are supposed to be double quotes but when I create my variable like so :
Command = "'Select * From " + Filename + "')" it doesn't work and it won't let me invert the quotation marks... could this be done with ASCII??? If so how?

I have tried it with both + and &

Thanks!

maddchad
01-06-2009, 02:21 PM
Managed to work it out, the problem was that "Command" is a reserved word so here is the final code if anyone cares...



Sub DiscountQuery()
'
' Discount Query
' Macro recorded 1/2/2009 by Support7
'
'
Dim Month As String
Dim Day As String
Dim Year As String
Dim Filename As String
Dim Command As String
Dim Foldername As String

Sheets("DiscountQuery").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents

Month = Range("c1").Value
If Len(Month) = 1 Then
Month = "0" & Month
Else
End If

Day = Range("b1").Value
If Len(Day) = 1 Then
Day = "0" & Day
Else
End If

Year = Range("d1").Value
Year = Right(Year, 2)
Foldername = Range("a1").Value

Filename = Foldername & Month & Day & Year
Commd = "Select * From " & Filename

Sheets("DiscountQuery").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=c:\DDWIN\Data\OR;SourceType=DBF;Exclusive=No;Back groundFetch=Yes;Collate=Machine;Nu" _
), Array("ll=Yes;Deleted=Yes;")), Destination:=Range("a2"))
.CommandText = Array(Commd)
.Name = "+Connect to New Data Source"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

End With
End Sub



Habve a great day everyone!!!

maddchad
01-06-2009, 02:25 PM
forgot the Dim Command as String should be Dim Commd as String