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?

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" wrote in message
...
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?

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

"RB Smissaert" wrote:

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" wrote in message
...
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?

.

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


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

"RB Smissaert" wrote:

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" wrote in message
...
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?

.