.odc connection managing with vba in excel 2003?

[Mike Coleman]

Hello fellow coders. I created a pretty slick app the creates an access
database and imports all the excel files in a folder into that database. This
is for a report in excel so I am pulling data from access into excel with a
piviot table. I am using an .ODC file to save the connection as the folder
where the excel files and database are created will change as the app gets
distrubited to different people. Wrote it all up in 2007 and saved in 2003
file format client only has 2003. This code does not work in 2003.
Public Sub AddDataConnection()
Dim workbookconnect As WorkbookConnection
ActiveWorkbook.Connections.AddFromFile (folder_name)

End Sub


anyone know the excel 2003 equivlilant to get this to work?

 

[RB Smissaert]

As you are moving Excel data to Access and then back to Excel do you
really need Access?
One option is to setup an ADO connection with a .udl file.

RBS

 

[Mike Coleman]

Yes as there are many excel files that add up to over 100k rows. I will look
into the .udl file solution.

 

[RB Smissaert]

Yes as there are many excel files that add up to over 100k rows.
Also consider SQLite for storage or maybe text files. SQLite is very fast
and there is a good, free VB wrapper.

RBS