Create Form to import data from excel template

[kambeng02]

Hi all,

I have an excel template that a lot of people has been using. I need it to be compiled in Access database to keep all the record from everyone into just one database.

So, I would prefer to set an Access database with a Form that has a Browse button that will ask the user to select the filled excel template, then Access will choose the data in the file and append the record in the excel file into the existing tables in the database.

But the excel files has a few notes at top, so the real record starts at row 5. Is it doable? I have a basic to intermediate VBA knowledge. So, VBA coding should be fine to me.

Thanks in advance.

 

[Isskint]

the DoCmd.TransferSpreadsheet command has a Range argument. Provide this starting at row 5 and it will import with the headings.

I would suggest importing to a temporary table so you can run some checks before running an Append action query to add the new data to the actual table.

 

[kambeng02]

Thanks for the quick reply.

I will give it a try and see how it goes.

What about making a button in a form to open a browser, so we can select it to be the path of the excel file?

 

[Isskint]

That depends on the version of Access you are using. A97 and A03 you can use the filedialog method to do this. A07 and A10 do not have filedialog so you need to find an alternative file browser function. There are a few topics on this forum on that subject already.