By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,158 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,158 IT Pros & Developers. It's quick & easy.

Automating Excel from Access

P: 2
Hi everyone,

I will need to start importing data from an XML file into our Access database on a daily basis. I have tried to import the data directly into a table, but it separates out into different tables in the database. I have found that when I import it into an Excel spreadsheet, however, it stays together just fine.

My question is, how can I automate the process from within Access? Is it possible to tell Excel to import the data for multiple files into a single spreadsheet? I can handle the second part, transferring the spreadsheet into Access, but I'm not familiar with Excel automation.

I am using version 2003 of both programs on Windows.

Thanks!
Mar 16 '07 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You should be able to do it directly in Access using VBA. Open the table as a recordset and append the data from each import.

Here are a couple of tutorials for you to check out to see the kind of thing I mean.

Import csv (Comma Separated Value) File
Access VBA DAO recordset loop using two recordsets

Mary
Mar 16 '07 #2

ADezii
Expert 5K+
P: 8,638
Hi everyone,

I will need to start importing data from an XML file into our Access database on a daily basis. I have tried to import the data directly into a table, but it separates out into different tables in the database. I have found that when I import it into an Excel spreadsheet, however, it stays together just fine.

My question is, how can I automate the process from within Access? Is it possible to tell Excel to import the data for multiple files into a single spreadsheet? I can handle the second part, transferring the spreadsheet into Access, but I'm not familiar with Excel automation.

I am using version 2003 of both programs on Windows.

Thanks!
The ImportXML method allows developers to import XML data and/or schema information into Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 7.0 or later, or the Microsoft Jet database engine
Sample Code:
Expand|Select|Wrap|Line Numbers
  1. Application.ImportXML DataSource:="C:\Test\Employees.xml", ImportOptions:=acStructureAndData
.
Mar 17 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.