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

Excel tables to MSAccess

Hi all,

Grateful if anyone could show me how to convert the data in an Excel workbook with has three worksheets into an MSAccess table from MSAccess.

Preferably I would like to know if it can be done using vba.

I am attaching my sample excel file so it can be seen.

  • 3
  • 2
2 Solutions
PipMicAuthor Commented:
my apologies the heading "Date" is missing in each of the tables...
Rey Obrero (Capricorn1)Commented:
yes it can be done using vba,
docmd.transferspreadsheet acimport,8, "tableX", "c:\foldername\yourexcel.xls",true,"sheet1!"

now give more specific information on how you like the information from the excel file to look like in   access table?

this vba commands will import the content of the Excel to 3 tables, table1,table2,table3

DoCmd.TransferSpreadsheet acImport, 8, "table1", CurrentProject.Path & "\tablesforAccess.xls", True, "sheet1!"
DoCmd.TransferSpreadsheet acImport, 8, "table2", CurrentProject.Path & "\tablesforAccess.xls", True, "sheet2!"
DoCmd.TransferSpreadsheet acImport, 8, "table3", CurrentProject.Path & "\tablesforAccess.xls", True, "sheet3!"

if you want combine the three tables to one, run a make table query like this

Select A.*  Into FinalTable
SELECT table1.F1, table1.[1], table1.[2], table1.[3], table1.[4], table1.[5]
FROM table1
Union All
SELECT table2.F1, table2.[6], table2.[7], table2.[8], table2.[9], table2.[10]
FROM table2
Union All
SELECT table3.F1, table3.[11], table3.[12], table3.[13], table3.[14], table3.[15]
FROM table3
) as A
PipMicAuthor Commented:
Tried this one line but no luck:

DoCmd.TransferSpreadsheet acImport, 8, "table1", "c:\1\tablesforAccess.xls", True, "sheet1!"

Compile error
Invalid outside procedure
Rey Obrero (Capricorn1)Commented:
where did you place the code?

try placing the code in the click event of a command button.

private sub command0_click()

DoCmd.TransferSpreadsheet acImport, 8, "table1", "c:\1\tablesforAccess.xls", True, "sheet1!"

end sub
PipMicAuthor Commented:
very good
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

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