I've been linking excel data for use in access for years, but I wanted any
fresh opinions on the best way to do this.
I have a worksheet that needs a lot of formatting to get the data useable in
Access. This file is updated weekly so the data changes. In some simpler
worksheets, I can run an instance of excel vba in access to format it before
import, but with the new complex worksheet, it's hard to do, especially to
compare files and see if the file has been reformatted with the macro. I was
thinking about having an excel file import the source excel file and run a
formatting macro before I import it to Access. This would make it easier to
keep the code and check versions and formatting by doing it all in excel
before import.
Does anyone do this type of prep? Are there alternatives to doing it this
way? I'm good at Access code but it takes a while for me to wrap my head
around excel vb. If you think doing it this way is a good idea, could you
leave some code to import the source worksheet into a new worksheet...I've
got the formatting code and I can figure out the file comparison portion.
Thx

Not knowing where your source data comes from, hopefully one of these options
will work for you. Instead of exporting to excel, than linking to excel, try:

1. Link to your source data with an ODBC connection.
2. Import directly into Access from your souce.
3. Export to a text file (I prefer csv).

--
Thanks, Kevin


"Maarkr" wrote:

I've been linking excel data for use in access for years, but I wanted any
fresh opinions on the best way to do this.
I have a worksheet that needs a lot of formatting to get the data useable in
Access. This file is updated weekly so the data changes. In some simpler
worksheets, I can run an instance of excel vba in access to format it before
import, but with the new complex worksheet, it's hard to do, especially to
compare files and see if the file has been reformatted with the macro. I was
thinking about having an excel file import the source excel file and run a
formatting macro before I import it to Access. This would make it easier to
keep the code and check versions and formatting by doing it all in excel
before import.
Does anyone do this type of prep? Are there alternatives to doing it this
way? I'm good at Access code but it takes a while for me to wrap my head
around excel vb. If you think doing it this way is a good idea, could you
leave some code to import the source worksheet into a new worksheet...I've
got the formatting code and I can figure out the file comparison portion.
Thx