Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 1004 general odbc error excel 2003 vba
hi,
the following is a macro i recorded. it's querying an myob database. it works fine if i don't use variable sInvoice in the where clause. i.e. if i replace with value '1234'. In its current state i get the run time error at .Refresh BackgroundQuery:=False Any help would be appreciated!! Thanks, Dim sInvoice As String Dim sInvoiceType As String sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number") sInvoice = "'" & sInvoice & "'" Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND (Sales.InvoiceStatusID='Q'))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 1004 general odbc error excel 2003 vba
should read..
... & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" ) sInvoice was left as text in your query, not converted to a value "Mentos" wrote: hi, the following is a macro i recorded. it's querying an myob database. it works fine if i don't use variable sInvoice in the where clause. i.e. if i replace with value '1234'. In its current state i get the run time error at .Refresh BackgroundQuery:=False Any help would be appreciated!! Thanks, Dim sInvoice As String Dim sInvoiceType As String sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number") sInvoice = "'" & sInvoice & "'" Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND (Sales.InvoiceStatusID='Q'))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 1004 general odbc error excel 2003 vba
Many thanks for your quick reply Kevin. That has definitely fixed variable
sInvoice. It is again erroring with the same error message, but this time, with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am leaving it as a string but obviously this isn't correct. Can you kindly shed some light into this? Regards, Dim sInvoice As String Dim sInvoiceType As String sInvoice = Worksheets("Sheet1").Range("B1").Value sInvoiceType = Worksheets("Sheet1").Range("B2").Value Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID = sInvoiceType ))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub -- MS SBS 2003 SP2 "Kevin Beckham" wrote: should read.. .. & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" ) sInvoice was left as text in your query, not converted to a value "Mentos" wrote: hi, the following is a macro i recorded. it's querying an myob database. it works fine if i don't use variable sInvoice in the where clause. i.e. if i replace with value '1234'. In its current state i get the run time error at .Refresh BackgroundQuery:=False Any help would be appreciated!! Thanks, Dim sInvoice As String Dim sInvoiceType As String sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number") sInvoice = "'" & sInvoice & "'" Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND (Sales.InvoiceStatusID='Q'))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 1004 general odbc error excel 2003 vba
On Jul 5, 2:32 am, Mentos wrote:
Many thanks for your quick reply Kevin. That has definitely fixed variable sInvoice. It is again erroring with the same error message, but this time, with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am leaving it as a string but obviously this isn't correct. Can you kindly shed some light into this? Regards, Dim sInvoice As String Dim sInvoiceType As String sInvoice = Worksheets("Sheet1").Range("B1").Value sInvoiceType = Worksheets("Sheet1").Range("B2").Value Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID = sInvoiceType ))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub -- MS SBS 2003 SP2 "Kevin Beckham" wrote: should read.. .. & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" ) sInvoice was left as text in your query, not converted to a value "Mentos" wrote: hi, the following is a macro i recorded. it's querying an myob database. it works fine if i don't use variable sInvoice in the where clause. i.e. if i replace with value '1234'. In its current state i get the run time error at .Refresh BackgroundQuery:=False Any help would be appreciated!! Thanks, Dim sInvoice As String Dim sInvoiceType As String sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number") sInvoice = "'" & sInvoice & "'" Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND (Sales.InvoiceStatusID='Q'))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub- Hide quoted text - - Show quoted text - ") AND (Sales.InvoiceStatusID = sInvoiceType ))" _ Because you have sInvoiceType inside the quotes, it is not a variable but a litteral string. In the original example, the field InvoiceStatusID is a string so I am assuming that sInvoiceType is a string variable holding the letter Q (or something) So that line should be written as: ") AND (Sales.InvoiceStatusID = '" & sInvoiceType & "' ))" _ Note that there are single quotes inside the double quotes so that the result has quotes around the contents of the variable sInvoiceType and results in something like: Sales.InvoiceStatusID = 'Q' Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 1004 general odbc error excel 2003 vba
Thank you so much, it now works! Regards, -- MS SBS 2003 SP2 " wrote: On Jul 5, 2:32 am, Mentos wrote: Many thanks for your quick reply Kevin. That has definitely fixed variable sInvoice. It is again erroring with the same error message, but this time, with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am leaving it as a string but obviously this isn't correct. Can you kindly shed some light into this? Regards, Dim sInvoice As String Dim sInvoiceType As String sInvoice = Worksheets("Sheet1").Range("B1").Value sInvoiceType = Worksheets("Sheet1").Range("B2").Value Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID = sInvoiceType ))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub -- MS SBS 2003 SP2 "Kevin Beckham" wrote: should read.. .. & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" ) sInvoice was left as text in your query, not converted to a value "Mentos" wrote: hi, the following is a macro i recorded. it's querying an myob database. it works fine if i don't use variable sInvoice in the where clause. i.e. if i replace with value '1234'. In its current state i get the run time error at .Refresh BackgroundQuery:=False Any help would be appreciated!! Thanks, Dim sInvoice As String Dim sInvoiceType As String sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number") sInvoice = "'" & sInvoice & "'" Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND (Sales.InvoiceStatusID='Q'))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub- Hide quoted text - - Show quoted text - ") AND (Sales.InvoiceStatusID = sInvoiceType ))" _ Because you have sInvoiceType inside the quotes, it is not a variable but a litteral string. In the original example, the field InvoiceStatusID is a string so I am assuming that sInvoiceType is a string variable holding the letter Q (or something) So that line should be written as: ") AND (Sales.InvoiceStatusID = '" & sInvoiceType & "' ))" _ Note that there are single quotes inside the double quotes so that the result has quotes around the contents of the variable sInvoiceType and results in something like: Sales.InvoiceStatusID = 'Q' Peter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
run time error 1004 general odbc error excel 2003 vba
Hi,
We have also similar problem with our makro. Can you help us? The wierd thing is that sometimes it is working (makro is schaduled to run in every half an hour). Debug is highlighting Selection.QueryTable.Refresh BackgroundQuery:=False Sub UpdateReport() 'Cancel screenuodating, set start-criterias and show all sheets Application.ScreenUpdating = False Sheets("Instructions").Select AutoWeekChangeActive = Range("B6").Value RunWeekChange = 0 Call ShowSheets 'Update sheet Produced QTY (PLPRODMGD) Sheets("PLPRODMGD").Select ActiveSheet.AutoFilterMode = off Range("A4:H2000").Select Selection.ClearContents Selection.Borders.LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.Font.Bold = False Range("A4").Select Range("B3").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("B3").Select If ActiveCell.Offset(1, 0) = "" Then ActiveCell.Offset(2, 0).Select Else Selection.End(xlDown).Select End If ActiveCell.Offset(0, -1).FormulaR1C1 = "1" Range("A4").Select ActiveCell.Formula = "=TEXT(D4&E4&B4,""#"")" Range(Selection, Selection.End(xlDown)).Select Selection.FillDown Range("A3").Select 'Update sheet New production orders (PLPODATO) Sheets("PLPODATO").Select ActiveSheet.AutoFilterMode = off Range("A4:K2000").Select Selection.ClearContents Selection.Borders.LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.Font.Bold = False Range("A4").Select Range("D3").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("D3").Select If ActiveCell.Offset(1, 0) = "" Then ActiveCell.Offset(2, 0).Select Else Selection.End(xlDown).Select End If ActiveCell.Offset(0, -1).FormulaR1C1 = "1" ActiveCell.Offset(0, -2).FormulaR1C1 = "1" ActiveCell.Offset(0, -3).FormulaR1C1 = "1" Range("A4").Select ActiveCell.Formula = "=TEXT(F4&G4&D4,""#"")" Range("B4").Select ActiveCell.Formula = "=TEXT(F4&G4&D4&""-""&C4,""#"")" Range("C4").Select ActiveCell.Formula = "=IF(H4="""","""",IF(H4=""DK"",""DK"",IF(H4=""PL"" ,""PL"",IF(H4=""GB"",""UK"",IF(H4=""FR"",""FR"","" Export"")))))" Range("A4:C4").Select Range(Selection, Selection.End(xlDown)).Select Selection.FillDown Range("A3").Select 'Update sheet New order proposals (PLPROPOSAL) Sheets("PLPROPOSAL").Select ActiveSheet.AutoFilterMode = off Range("A4:N2000").Select Selection.ClearContents Selection.Borders.LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.Font.Bold = False Range("A4").Select Range("E3").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("E3").Select If ActiveCell.Offset(1, 0) = "" Then ActiveCell.Offset(2, 0).Select Else Selection.End(xlDown).Select End If ActiveCell.Offset(0, -1).FormulaR1C1 = "1" ActiveCell.Offset(0, -2).FormulaR1C1 = "1" ActiveCell.Offset(0, -3).FormulaR1C1 = "1" ActiveCell.Offset(0, -4).FormulaR1C1 = "1" Range("A4").Select ActiveCell.Formula = "=CONCATENATE(D4,""-"",TEXT(B4,""RRRRMMDD""))" ActiveCell.Offset(0, 1).Formula = "=IF(ISERROR(VLOOKUP(C4,'Non-production'!$A$11:$A$70,1,FALSE)),WORKDAY(C4,0,'No n-production'!$A$10:$A$70),WORKDAY(C4,-1,'Non-production'!$A$10:$A$70))" ActiveCell.Offset(0, 2).Formula = "=IF(OR(M4=""520"",M4=""540""),WORKDAY(DATE(LEFT(I 4,4),MID(I4,5,2),RIGHT(I4,2)),-3,'Non-production'!$A$10:$A$70),DATE(LEFT(I4,4),MID(I4,5, 2),RIGHT(I4,2)))" ActiveCell.Offset(0, 3).Formula = "=G4&H4&E4" Range("A4:D4").Select Range(Selection, Selection.End(xlDown)).Select Selection.FillDown Range("A3").Select 'Update sheet Released production orders (PLPOLOAD) Sheets("PLPOLOAD").Select ActiveSheet.AutoFilterMode = off Range("A4:P2000").Select Selection.ClearContents Selection.Borders.LineStyle = xlNone Selection.Interior.ColorIndex = xlNone Selection.Font.Bold = False Range("A4").Select Range("C3").Select Selection.QueryTable.Refresh BackgroundQuery:=False Range("C3").Select If ActiveCell.Offset(1, 0) = "" Then ActiveCell.Offset(2, 0).Select Else Selection.End(xlDown).Select End If ActiveCell.Offset(0, -1).FormulaR1C1 = "1" ActiveCell.Offset(0, -2).FormulaR1C1 = "1" Range("A4").Select ActiveCell.Formula = "=IF(L4<""40"",TEXT(E4&F4&C4&""-""&20&""-""&M4&""-""&O4&""-""&G4,""#""),TEXT(E4&F4&C4&""-""&40&""-""&M4&""-""&O4&""-""&G4,""#""))" ActiveCell.Offset(0, 1).Formula = "=TEXT(E4&F4&C4&""-""&IF(L4=""40"",40,IF(L4<""40"",""20"",L4))&" "-""&M4&""-""&G4,""#"")" Range("A4:B4").Select Range(Selection, Selection.End(xlDown)).Select Selection.FillDown Range("A3").Select 'Insert todays date and place cursor in correct cell Sheets("Control").Select IDAG = Range("A27").Value Sheets("Information").Select Range("Q24:Q33").Copy Range("J23").Select If ActiveCell = IDAG Then ActiveCell.Offset(1, 0).Select ElseIf ActiveCell.Offset(0, 1) = IDAG Then ActiveCell.Offset(1, 1).Select ElseIf ActiveCell.Offset(0, 2) = IDAG Then ActiveCell.Offset(1, 2).Select ElseIf ActiveCell.Offset(0, 3) = IDAG Then ActiveCell.Offset(1, 3).Select ElseIf ActiveCell.Offset(0, 4) = IDAG Then ActiveCell.Offset(1, 4).Select Else ActiveCell.Offset(1, 10).Select 'If weekday is not available, set criteria to run weekchange to 1 (=Active) If AutoWeekChangeActive = "Yes" Then RunWeekChange = 1 End If End If 'Insert totals for produced orders Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("Q39:Q44").Copy ActiveCell.Offset(15, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Insert totals for order intake Range("Q52:Q61").Copy ActiveCell.Offset(13, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'Insert totals for order split of order intake Range("Q67:Q77").Copy ActiveCell.Offset(15, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False 'Change week if activated If RunWeekChange = 1 Then Call ShowSheets Call MakeWeekChange Call HideSheets End If Range("A4").Select 'Insert time of updating Sheets("FB1").Select Range("C1") = Now 'Hide unneeded shets Call HideSheets 'Restart screen-updatinga Application.CutCopyMode = False Application.ScreenUpdating = True End Sub On Thursday, July 05, 2007 2:04 AM mento wrote: hi, the following is a macro i recorded. it's querying an myob database. it works fine if i don't use variable sInvoice in the where clause. i.e. if i replace with value '1234'. In its current state i get the run time error at .Refresh BackgroundQuery:=False Any help would be appreciated!! Thanks, Dim sInvoice As String Dim sInvoiceType As String sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number") sInvoice = "'" & sInvoice & "'" Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND (Sales.InvoiceStatusID='Q'))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub On Thursday, July 05, 2007 2:58 AM KevinBeckha wrote: should read.. .. & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" ) sInvoice was left as text in your query, not converted to a value "Mentos" wrote: On Thursday, July 05, 2007 4:32 AM mento wrote: Many thanks for your quick reply Kevin. That has definitely fixed variable sInvoice. It is again erroring with the same error message, but this time, with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am leaving it as a string but obviously this isn't correct. Can you kindly shed some light into this? Regards, Dim sInvoice As String Dim sInvoiceType As String sInvoice = Worksheets("Sheet1").Range("B1").Value sInvoiceType = Worksheets("Sheet1").Range("B2").Value Sheets("Sheet2").Select With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;", Destination _ :=Range("A1")) .CommandText = Array( _ "SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID, Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber, Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1, Sales.DeliveryAddressL" _ , _ "ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice & ") AND (Sales.InvoiceStatusID = sInvoiceType ))" _ ) .Name = "Quote header" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Sheets("Sheet1").Select Range("B20").Select End Sub -- MS SBS 2003 SP2 "Kevin Beckham" wrote: On Thursday, July 05, 2007 9:17 AM par_6005 wrote: On Jul 5, 2:32 am, Mentos wrote: Because you have sInvoiceType inside the quotes, it is not a variable but a litteral string. In the original example, the field InvoiceStatusID is a string so I am assuming that sInvoiceType is a string variable holding the letter Q (or something) So that line should be written as: ") AND (Sales.InvoiceStatusID = '" & sInvoiceType & "' ))" _ Note that there are single quotes inside the double quotes so that the result has quotes around the contents of the variable sInvoiceType and results in something like: Sales.InvoiceStatusID = 'Q' Peter On Thursday, July 05, 2007 11:16 PM mento wrote: Thank you so much, it now works! Regards, -- MS SBS 2003 SP2 Submitted via EggHeadCafe Stock Quotes via jQuery-enabled WCF Service, JSON, and jQuery Templates http://www.eggheadcafe.com/tutorials...templates.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error 1004 - General ODBC Error | Excel Programming | |||
1004 General ODBC Error | Excel Programming | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
Runtime error '1004' General ODBC error | New Users to Excel | |||
Run time error '1004': Generaol ODBC error | Excel Programming |