VBA: Using VBA in Access to import weekly Excel spreadsheet to an existing table

Hi,

         I have a weekly task where I have to compare 3 Excels sheets from the current and previous week for about 30+ columns. I'm looking for a VBA solution to import the Excel file to an existing table (so my queries can run) while using the 1st row as the headers.

The major problem I'm having is the Excel columns may not be the same place each week, and worst case, the spelling of the column may not be consistent week over week (I think I can figure out the error handling part).
        Any advice? Thanks.

Shino_skayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sstampfCommented:
There are several ways in which you can do this but the best approach (I think) would be to craete a link to your excel table within your access database. The second approach can be to create a simple macro which will delete the exsiting table, import the data from excel file and store the info into a new table with the same name. Third approach would be to use VBA.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shino_skayAuthor Commented:
Is there a method to import only specific colum names to an existing table? The reason I ask is I have queries based off this existing table and if I use the import command, all the columns have to line up or there'll be an error. Thanks.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
i'll use a temptable to received the imported excel file
(import the excel file as it is)
* use the temptable in the query to append the records to the final table
* delete the temptable
0
Helen FeddemaCommented:
I would agree with capricorn1.  As for the inconsistency in the Excel worksheets, I recommend creating an Excel template (.xlt or .xltx) file, and require your users to use it for entering data, so you won't have to worry about columns in different places or with different names.
0
Helen FeddemaCommented:
Alternatively, you could write some Excel VBA to create a new sheet in the current workbook and set up the columns as needed, which could be run from a hot key or a menu item.
0
Shino_skayAuthor Commented:
good morning everyone. Thanks for your feedback. I like Helen and capricorn's idea. I think that's the best choice for what I have in mind. Do you guys know the syntax to import an a sheet from Excel?

0
Rey Obrero (Capricorn1)Commented:
this will import sheet1 to tmpTable

docmd.transferspreadsheet acimport, acSpreadsheetTypeExcel9, "tmpTable", "c:\myexcel.xls", true, "sheet1!"
0
Shino_skayAuthor Commented:
hi Capricorn1,

Without opening a new question, do you know how to use the code above for a worksheet's codename?

I'm afraid to use the worksheet's name as the end user might change it. Thank you.
0
Rey Obrero (Capricorn1)Commented:
what do you mean by worksheet's codename?
0
Helen FeddemaCommented:
You can use the range, as a string, like this:
   DoCmd.TransferSpreadsheet transfertype:=acImport, _
      spreadsheettype:=acSpreadsheetTypeExcel9, _
      tablename:=strTable, _
      FileName:=strWorkbook, _
      hasfieldnames:=True, _
      Range:=strRange
0
Shino_skayAuthor Commented:
The worksheet name is "Step1 - Insert Bloomberg Data", in the VBE, the codename is "shtBondData". I included a picture in the attached word doc.
vbe-view.doc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.