• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

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.

0
Shino_skay
Asked:
Shino_skay
  • 4
  • 3
  • 3
  • +1
5 Solutions
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now