Solved

Excel tables to MSAccess

Posted on 2013-11-06
5
409 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

862 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

24 Experts available now in Live!

Get 1:1 Help Now