Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTables Error
I have the following code in a macro. This has always worked and comtinues
to work with Excel 2002, but since I upgraded to Excel 2003 the BackgroundQuery Line causes an error. With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=dBASE Files;DefaultDir=C:\;DriverId=533;MaxBufferSize=20 48;PageTimeout=" _ ), Array("5;")), Destination:=Range("B27")) .CommandText = Array( _ "SELECT JOB_NO.CUSTCODE, JOB_NO.PROJ_DESCR, JOB_NO.PO_NUMBER, JOB_NO.DATE, JOB_NO.PROJMAN, JOB_NO.CUSTFORCD" & Chr(13) & "" & Chr(10) & "FROM `K:\DATA\DBDATA\JOBDATA`\JOB_NO.DBF JOB_NO WHERE(JOB_NO.JOB_NO=" & Worksheets("Creation").Range("B3").Value & ")" _ ) .Name = "Query from dBASE Files" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery = False <<--this line gets an error End With ___Microsoft Visual Basic ___ The error is Run-time error '1004': Application-defined or object-defined error What could be causing this, I did do a full install of Office 2003. Thanks for any help or suggestions. Sharlene England Network Administrator Westwood Companies Vernon, BC Canada. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTables Error
The actual line is as below (just so you don't tell me that it is syntax)
The colon is in the line the gets the error. .Refresh BackgroundQuery := False Please help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTables Error
Hi Sharlene ;
I'll take a stab at it. I would check and see if the DriverId 533 is still valid with the new version. In my experience, that type of error usuall shows up when the object did not ever get instanciated in the first place. If the driver number is not valid then that would cause it I think. Thanks, Greg -----Original Message----- I have the following code in a macro. This has always worked and comtinues to work with Excel 2002, but since I upgraded to Excel 2003 the BackgroundQuery Line causes an error. With ActiveSheet.QueryTables.Add(Connection:=Array (Array( _ "ODBC;DSN=dBASE Files;DefaultDir=C:\;DriverId=533;MaxBufferSize=2 048;PageT imeout=" _ ), Array("5;")), Destination:=Range("B27")) .CommandText = Array( _ "SELECT JOB_NO.CUSTCODE, JOB_NO.PROJ_DESCR, JOB_NO.PO_NUMBER, JOB_NO.DATE, JOB_NO.PROJMAN, JOB_NO.CUSTFORCD" & Chr(13) & "" & Chr(10) & "FROM `K:\DATA\DBDATA\JOBDATA`\JOB_NO.DBF JOB_NO WHERE (JOB_NO.JOB_NO=" & Worksheets("Creation").Range("B3").Value & ")" _ ) .Name = "Query from dBASE Files" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery = False <<-- this line gets an error End With ___Microsoft Visual Basic ___ The error is Run-time error '1004': Application-defined or object-defined error What could be causing this, I did do a full install of Office 2003. Thanks for any help or suggestions. Sharlene England Network Administrator Westwood Companies Vernon, BC Canada. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
QueryTables Error
It might be worth it to start working with ADO.
Plenty of examples of this available and probably easier and more powerful. RBS "Sharlene England" wrote in message ... I have the following code in a macro. This has always worked and comtinues to work with Excel 2002, but since I upgraded to Excel 2003 the BackgroundQuery Line causes an error. With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=dBASE Files;DefaultDir=C:\;DriverId=533;MaxBufferSize=20 48;PageTimeout=" _ ), Array("5;")), Destination:=Range("B27")) .CommandText = Array( _ "SELECT JOB_NO.CUSTCODE, JOB_NO.PROJ_DESCR, JOB_NO.PO_NUMBER, JOB_NO.DATE, JOB_NO.PROJMAN, JOB_NO.CUSTFORCD" & Chr(13) & "" & Chr(10) & "FROM `K:\DATA\DBDATA\JOBDATA`\JOB_NO.DBF JOB_NO WHERE(JOB_NO.JOB_NO=" & Worksheets("Creation").Range("B3").Value & ")" _ ) .Name = "Query from dBASE Files" .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery = False <<--this line gets an error End With ___Microsoft Visual Basic ___ The error is Run-time error '1004': Application-defined or object-defined error What could be causing this, I did do a full install of Office 2003. Thanks for any help or suggestions. Sharlene England Network Administrator Westwood Companies Vernon, BC Canada. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTables Error | Excel Programming | |||
Using Add Method of QueryTables gives error | Excel Programming | |||
With ActiveSheet.QueryTables.Add | Excel Programming | |||
QueryTables Interrupt if URL is Down | Excel Programming |