I want to load data from excel to access using xml

[Guest]

I have data in excel (not in columnar format) that I need pushed to an access
table. By pushed I mean when the user completes his file in excel that the
data gets sent to access. This would happen one record at a time. I have the
table in access. The xml schema and the data elements mapped into my excel
file. I can export the record to an xml data file and then import this file
to access - but can I skip the export to a data file and go directly to the
db?

 

[John Nurick]

Hi Deirdre,

I think the answer is "Yes, but you'd have to program the MSXML2
library yourself." ISTM if you're committed to XML it's simpler to
write and read the file because that uses built-in functions.

There shouldn't be much difficulty using Automation in Access VBA or
Excel VBA to have a single procedure make Excel export the data and
then have Access import it.

Or you can do it the usual way: write Excel VBA code that reads the
values of the various cells, assembles them into a SQL INSERT INTO
statement (append query) and executes this against the the database.

 

[Guest]

I really like xml because of the ease of mapping the fields (220 fields).
Not too sure what ISTM is, can you point me in the right direction so I can
research? Also, I'm using Excel/Access 2003 - fyi.

If I was to go the VBA route, would I be calling an Access macro from Excel?
The users need to be able to do all this in Excel. I was looking the code
up for this and it didn't seem too tough...

 

[Douglas J. Steele]

ISTM = "It Seems To Me"

 

[Guest]

Looking for help on the code to use Automation in Access VBA and/or Excel VBA
to have a single procedure make Excel export the data and then have Access
import it. Will need to have the export from excel be able to save and
replace existing xml data file. Will also have to run access import of the
saved xml data file from excel without opening access. I'm having trouble
with the module to automate the import xml data. Thanks

 

[John Nurick]

ImportXML is a method of the Access application object, so you (or
rather your Excel VBA code) will have to open Access. Maybe something
like this:


'Export from Excel. I guess you're using something like
ActiveWorkbook.SaveAsXML strXMLFileSpec, xmlMapMyMap

'Give the export a chance to finish (inc. delayed writes from the OS
DoEvents 'may need to put an explicit delay here

'Launch the Access database
Dim AccApp As Access.Application
Dim dbD As DAO.Database

Set AccApp = CreateObject(, "Access.Application")
AccApp.OpenCurrentDatabase "D:\Folder\MyDB.mdb"

'Import
AccApp.ImportXML strXMLFileSpec, 2 '2 = acAppendData

DoEvents

AccApp.Quit

Automating Access isn't exactly like as automating Word or Excel. This
http://support.microsoft.com/?id=210111 is worth bookmarking.

 

[Guest]

First let me say - thanks for all of your help so far. I really appreciate it.

Here is my export code from excel:

Private Sub PricingUpload_Current_Click()
ActiveWorkbook.XmlMaps("Current_Pricing").Export URL:= _
"\\fr1nas102\EAGFinance$\share\KBR\XML_Data\PricingUpload_Current.xml"
End Sub

This works fine. For Access I wrote the import xml code and a delete file
bit to remove the data file after loading (so that other users can use thie
macro in excel without getting an error message). Both pieces are in the one
macro in access that I want to run from excel. Macro works fine too. My db
has user level security but for this file's purposes I have a user name but
the password is blank.
Can I replace the import code with:
.DoCmd.RunMacro "ImportPricingData"


I was using this as an example and it wasn't working for me - not too sure
if it was the security on the db - or just me...

Sub OpenAccessMacro()

'Step 1: Declare your variables
Dim AC As Access.Application

'Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase ("C:\Integration\IntegrationDatabase.accdb")

'Step 3: Open the target report and send to Word
With AC
.DoCmd.RunMacro "MyMacro"
.Quit
End With

Workbooks.Open "C:\MyExcel_Output.XLSX"

End Sub

 

[John Nurick]

I hardly ever use macros - not least because they don't allow any
error handling - but as far as I know once you have an application
object and a current database you can use Application.DoCmd.RunMacro
to launch a macro.

However there are complications when it comes to automating a secured
database. There's inadequate sample code towards the bottom of the KB
article I referred you to (for one thing, it doesn't mention that
you'll often need to use the /wrkgrp option), and something rather
better at http://support.microsoft.com/kb/192919/ (although that too
can go wrong if other instances of Access are already running).

Possibly you'll be able to get away with something like this (I'm
assuming this is for "production" use by other users):

1) create a VBA function that
-uses the Commmand() function to get the path to the XML
file from the command line
-imports the data from the XML file
-if successful, deletes the file
-maybe makes an entry in a log file or table (esp. to
help troubleshooting)
-closes Access.

2) create a macro that launches the VBA function.

3) from the Excel code, use Shell() to execute a command line that
launches Access and includes all these switches:
/wrkgrp
/user
/pwd
/nostartup
/x to launch the macro
/cmd to pass the path to the XML file

4) have the Excel code loop until either the XML file no longer exists
(which means the import was successful) or so much time has passed
that it can be assumed that the import failed. Be sure to include
DoEvents
in the loop.

First let me say - thanks for all of your help so far. I really appreciate it.

Here is my export code from excel:

Private Sub PricingUpload_Current_Click()
ActiveWorkbook.XmlMaps("Current_Pricing").Export URL:= _
"\\fr1nas102\EAGFinance$\share\KBR\XML_Data\PricingUpload_Current.xml"
End Sub

This works fine. For Access I wrote the import xml code and a delete file
bit to remove the data file after loading (so that other users can use thie
macro in excel without getting an error message). Both pieces are in the one
macro in access that I want to run from excel. Macro works fine too. My db
has user level security but for this file's purposes I have a user name but
the password is blank.
Can I replace the import code with:
.DoCmd.RunMacro "ImportPricingData"


I was using this as an example and it wasn't working for me - not too sure
if it was the security on the db - or just me...

Sub OpenAccessMacro()

'Step 1: Declare your variables
Dim AC As Access.Application

'Step 2: Start Access and open the target database
Set AC = New Access.Application
AC.OpenCurrentDatabase ("C:\Integration\IntegrationDatabase.accdb")

'Step 3: Open the target report and send to Word
With AC
.DoCmd.RunMacro "MyMacro"
.Quit
End With

Workbooks.Open "C:\MyExcel_Output.XLSX"

End Sub

ImportXML is a method of the Access application object, so you (or
rather your Excel VBA code) will have to open Access. Maybe something
like this:


'Export from Excel. I guess you're using something like
ActiveWorkbook.SaveAsXML strXMLFileSpec, xmlMapMyMap

'Give the export a chance to finish (inc. delayed writes from the OS
DoEvents 'may need to put an explicit delay here

'Launch the Access database
Dim AccApp As Access.Application
Dim dbD As DAO.Database

Set AccApp = CreateObject(, "Access.Application")
AccApp.OpenCurrentDatabase "D:\Folder\MyDB.mdb"

'Import
AccApp.ImportXML strXMLFileSpec, 2 '2 = acAppendData

DoEvents

AccApp.Quit

Automating Access isn't exactly like as automating Word or Excel. This
http://support.microsoft.com/?id=210111 is worth bookmarking.

 

[Guest]

How about a different tactic. Could I export the xml data to a file - but
always append? So that I could run a macro in access which would be
scheduled for every day to pull in the xml data, and then clear the file so
that it would be empty to start all over again? This way Acees wouldn't have
to be incolved in the code from excel at all? I just need something where
multiple users (each day) can drop records of data, and then the db can pick
up in the evening...

I hardly ever use macros - not least because they don't allow any
error handling - but as far as I know once you have an application
object and a current database you can use Application.DoCmd.RunMacro
to launch a macro.

However there are complications when it comes to automating a secured
database. There's inadequate sample code towards the bottom of the KB
article I referred you to (for one thing, it doesn't mention that
you'll often need to use the /wrkgrp option), and something rather
better at http://support.microsoft.com/kb/192919/ (although that too
can go wrong if other instances of Access are already running).

Possibly you'll be able to get away with something like this (I'm
assuming this is for "production" use by other users):

1) create a VBA function that
-uses the Commmand() function to get the path to the XML
file from the command line
-imports the data from the XML file
-if successful, deletes the file
-maybe makes an entry in a log file or table (esp. to
help troubleshooting)
-closes Access.

2) create a macro that launches the VBA function.

3) from the Excel code, use Shell() to execute a command line that
launches Access and includes all these switches:
/wrkgrp
/user
/pwd
/nostartup
/x to launch the macro
/cmd to pass the path to the XML file

4) have the Excel code loop until either the XML file no longer exists
(which means the import was successful) or so much time has passed
that it can be assumed that the import failed. Be sure to include
DoEvents
in the loop.

 

[Guest]

Or - this code works to where it opens the database, but then I get an
object/application defined error when I include the code for running the
macro:

Private Sub Command1_Click()
Dim accObj As Access.application
Dim application As String, dbs As String, workgroup As String
Dim user As String, password As String
Dim x
Dim cmd

application = "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"
dbs = "\\fr1nas102\EAGFinance$\share\KBR\KBR_DB.mdb"
workgroup = "\\fr1nas102\EAGFinance$\share\KBR\Secure.mdw "
user = "Pricing"
password = ""
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup
/user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34),
vbMinimizedFocus)
Set accObj = GetObject(, "Access.Application")
***
DoCmd.RunMacro "ImportPricingData"


End Sub

Also, if another user's Access is a different path on their machine will
that affect the result?

I hardly ever use macros - not least because they don't allow any
error handling - but as far as I know once you have an application
object and a current database you can use Application.DoCmd.RunMacro
to launch a macro.

However there are complications when it comes to automating a secured
database. There's inadequate sample code towards the bottom of the KB
article I referred you to (for one thing, it doesn't mention that
you'll often need to use the /wrkgrp option), and something rather
better at http://support.microsoft.com/kb/192919/ (although that too
can go wrong if other instances of Access are already running).

Possibly you'll be able to get away with something like this (I'm
assuming this is for "production" use by other users):

1) create a VBA function that
-uses the Commmand() function to get the path to the XML
file from the command line
-imports the data from the XML file
-if successful, deletes the file
-maybe makes an entry in a log file or table (esp. to
help troubleshooting)
-closes Access.

2) create a macro that launches the VBA function.

3) from the Excel code, use Shell() to execute a command line that
launches Access and includes all these switches:
/wrkgrp
/user
/pwd
/nostartup
/x to launch the macro
/cmd to pass the path to the XML file

4) have the Excel code loop until either the XML file no longer exists
(which means the import was successful) or so much time has passed
that it can be assumed that the import failed. Be sure to include
DoEvents
in the loop.

 

[John Nurick]

Unlike ordinary text files, XML files have a hierarchical structure
and it's not always simple to append stuff. I don't know whether
Excel's built-in XML export feature has an 'append' option.

How about a different tactic. Could I export the xml data to a file - but
always append? So that I could run a macro in access which would be
scheduled for every day to pull in the xml data, and then clear the file so
that it would be empty to start all over again? This way Acees wouldn't have
to be incolved in the code from excel at all? I just need something where
multiple users (each day) can drop records of data, and then the db can pick
up in the evening...

I hardly ever use macros - not least because they don't allow any
error handling - but as far as I know once you have an application
object and a current database you can use Application.DoCmd.RunMacro
to launch a macro.

However there are complications when it comes to automating a secured
database. There's inadequate sample code towards the bottom of the KB
article I referred you to (for one thing, it doesn't mention that
you'll often need to use the /wrkgrp option), and something rather
better at http://support.microsoft.com/kb/192919/ (although that too
can go wrong if other instances of Access are already running).

Possibly you'll be able to get away with something like this (I'm
assuming this is for "production" use by other users):

1) create a VBA function that
-uses the Commmand() function to get the path to the XML
file from the command line
-imports the data from the XML file
-if successful, deletes the file
-maybe makes an entry in a log file or table (esp. to
help troubleshooting)
-closes Access.

2) create a macro that launches the VBA function.

3) from the Excel code, use Shell() to execute a command line that
launches Access and includes all these switches:
/wrkgrp
/user
/pwd
/nostartup
/x to launch the macro
/cmd to pass the path to the XML file

4) have the Excel code loop until either the XML file no longer exists
(which means the import was successful) or so much time has passed
that it can be assumed that the import failed. Be sure to include
DoEvents
in the loop.

 

[John Nurick]

Hi Deirdre,

Once you've got accObj pointed at the instance of Access, you'll need
to do
accObj.DoCmd.RunMacro "ImportPricingData"
.. Also, you need to include a loop to make Excel wait while Windows
loads Access and Access opens the mdw and mdb files, as shown in the
two sample procedures I pointed you to.

If you hard-code the path to msaccess.exe then yes, your code will
fail if used on a machine where it's installed in a different place
(and each version of Access ends to use a different path. The
fFindEXE() function at http://www.mvps.org/access/api/api0023.htm
should be able to find the right path for you.

Or - this code works to where it opens the database, but then I get an
object/application defined error when I include the code for running the
macro:

Private Sub Command1_Click()
Dim accObj As Access.application
Dim application As String, dbs As String, workgroup As String
Dim user As String, password As String
Dim x
Dim cmd

application = "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"
dbs = "\\fr1nas102\EAGFinance$\share\KBR\KBR_DB.mdb"
workgroup = "\\fr1nas102\EAGFinance$\share\KBR\Secure.mdw "
user = "Pricing"
password = ""
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup
/user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34),
vbMinimizedFocus)
Set accObj = GetObject(, "Access.Application")
***
DoCmd.RunMacro "ImportPricingData"


End Sub

Also, if another user's Access is a different path on their machine will
that affect the result?

I hardly ever use macros - not least because they don't allow any
error handling - but as far as I know once you have an application
object and a current database you can use Application.DoCmd.RunMacro
to launch a macro.

However there are complications when it comes to automating a secured
database. There's inadequate sample code towards the bottom of the KB
article I referred you to (for one thing, it doesn't mention that
you'll often need to use the /wrkgrp option), and something rather
better at http://support.microsoft.com/kb/192919/ (although that too
can go wrong if other instances of Access are already running).

Possibly you'll be able to get away with something like this (I'm
assuming this is for "production" use by other users):

1) create a VBA function that
-uses the Commmand() function to get the path to the XML
file from the command line
-imports the data from the XML file
-if successful, deletes the file
-maybe makes an entry in a log file or table (esp. to
help troubleshooting)
-closes Access.

2) create a macro that launches the VBA function.

3) from the Excel code, use Shell() to execute a command line that
launches Access and includes all these switches:
/wrkgrp
/user
/pwd
/nostartup
/x to launch the macro
/cmd to pass the path to the XML file

4) have the Excel code loop until either the XML file no longer exists
(which means the import was successful) or so much time has passed
that it can be assumed that the import failed. Be sure to include
DoEvents
in the loop.