Solved

Excel tables to MSAccess

Posted on 2013-11-06
5
404 Views
Last Modified: 2013-11-27
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.

Thanks
tablesforAccess.xls
0
Comment
Question by:PipMic
  • 3
  • 2
5 Comments
 

Author Comment

by:PipMic
ID: 39628067
my apologies the heading "Date" is missing in each of the tables...
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39628075
yes it can be done using vba,
using
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
 from
(
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
0
 

Author Comment

by:PipMic
ID: 39630478
Tried this one line but no luck:

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

Compile error
Invalid outside procedure
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 39630779
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
0
 

Author Closing Comment

by:PipMic
ID: 39681191
very good
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now