problems with a 2003 project not running in 2000

[ribbs2521]

Can anyone tell me if there is a way around this statement in 2000?

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.

 

[SydneyGeek]

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

 

[ribbs2521]

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

    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

 

[SydneyGeek]

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

 

[ribbs2521]

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.