problems with a 2003 project not running in 2000

ribbs2521

Board Regular
Joined
Sep 8, 2007
Messages
158
Can anyone tell me if there is a way around this statement in 2000?

Code:
AnObject.SourceDataFile
It's part of an import query from another excel file but I get a runtime error when it runs in 2000. This is part of a widely used program and I need it to work in both versions.

Any help is appreciated.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Are you missing a Class Module by any chance? AnObject and SourceDataFile are not terms that I recognise and I'm guessing that they may be related to a custom class.

Denis
 
Upvote 0
Sorry, I didn't have the code with me when I posted it. Here is the code I use to create an import from an excel file using a query. It's actually part of a recorded macro. I think the problem is that 2000 does not support import from excel, in which case you need to create tables. I'm not sure though, that's why I posted

Code:
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=V:\Service Rochester\Idle + Labor Reports\ENI Product Fam" _
        , _
        "ily\product - family assignments.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB" _
        , _
        ":Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Par" _
        , _
        "tial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;J" _
        , _
        "et OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet " _
        , "OLEDB:SFP=False"), Destination:=Range("A1"))
        .CommandType = xlCmdTable
        .CommandText = Array("'Prod-Family$'")
        .Name = "product - family assignments"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "V:\Service Rochester\Idle + Labor Reports\ENI Product Family\product - family assignments.xls"
        .Refresh BackgroundQuery:=False
    End With
 
Upvote 0
OK, so it looks like you are creating a query for external data on the fly. Is there a particular need to create the query each time, or could you just refresh it instead?

What is the runtime error, and if you press Debug, which line is highlighted?

Also, I normally try to stay away from + and & symbols in directory, file and sheet names. VBA can (and often will) interpret them as concatenation symbols, instead of being part of the name.

Denis
 
Upvote 0
The only reason I was recreating the query was because I had 2 other queries that were text files and when I tried to refresh them it would ask to verify the path. I didn't want that so I had it create the query each time. I tried using refresh with the excel query and it works. Thanks for all of your help.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top