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