Users import from Excel to Access

I'm running into problems with trying to create a macro and I was needing some advise... I think it would be better if it was done in VBA, but I'm not familiar with VBA... any help would be appreciated!

I'm trying to create a macro that will do the following...

1. Prompt the user to browse for an excel file.
I could use the "Run Command" -> "Import", but I'm trying to automate this process to keep the user interaction down.

2. Import the excel file (2 worksheets) into 2 table in access. The worksheets first row is a header row.
This is easy to do using the "Import" command to bring up the Import Wizard, but since I want to stay away from the wizard, I believe that I should use the "Transfer Spreadsheet" command". The problem I run into here, is that I'm required to put the filename and path into the "File Name" property. I am not sure how to set this up since I want to user to browse for the file.

Should I use VBA or can i do this with macros? Are there better commands to use or a better method to do this?
If i have to use VBA, can anyone help me or give me some further advice...

Thanks for any help you can offer.

discussion with code and other linkks

http://www.access-programmers.co.uk/forums/showthread.php?p=355255#post355255

hi All,

Is there a size limitation on how many rows an Excel 2007 spreadsheet can have when importing to Access 2007? I have about 9300 rows of data in Excel(not big) and the eventual size will be much bigger.

But even now, it's taking forever to import just 9300 rows of Excel data. Geez. I did it through the import wizard as well as through the TransferSpreadSheet function, which normally works like a charm.

In fact, it's still hasn't completed. I killed the process the first few times. But now I'm going to let it run to see how long it takes to import. Something is wrong... Shouldn't take this long...

Any ideas/suggestions?

thanks!

flyinghippo99 said:

hi All,

Is there a size limitation on how many rows an Excel 2007 spreadsheet can have when importing to Access 2007? I have about 9300 rows of data in Excel(not big) and the eventual size will be much bigger.


thanks!

Click to expand...

It really shouldnt take that long. I use code to import sheets that can be 20,000 rows with 27 columns and it finishes in under 30 seconds.
How are you trying to import this?

JPearson said:

It really shouldnt take that long. I use code to import sheets that can be 20,000 rows with 27 columns and it finishes in under 30 seconds.
How are you trying to import this?

Click to expand...

It works now. Could have been strange instability with a copy of Access I was running. Now, it zips through the import. Very strange.

thnx,

flyinghippo99