ExcelGirl,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
<o
> </o
>
Here is some code that might help you.<o
></o
>
The trick is always to not make things more complex than needed.<o
></o
>
In this example the only things you have to look after are:<o
></o
>
<o
> </o
>
- take a good look at how the queries are written to understand how you can rewrite them for your situation<o
></o
>
- make sure your named ranges have column headers, this is a key thing to avoid really big trouble<o
></o
>
<o
> </o
>
You’ll see two variants for the same. One using <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com
ffice:smarttags" /><st1:City w:st="on"><st1
lace w:st="on">ADO</st1
lace></st1:City> the other using the Access ‘do command’ structure. It’s really up to you which one to use. <st1
lace w:st="on"><st1:City w:st="on">ADO</st1:City></st1
lace> is faster and more structured, but as we are not talking about really big amounts of complex data, the Access native command will be more than sufficient. <o
></o
>
<o
> </o
>
In this example we have an Excel sheet with two named ranges, DataSetA and DataSetB.<o
></o
>
We want to write these to two tables, tblDataSetA and tblDataSetB.<o
></o
>
<o
> </o
>
tblDataSetA has two fields: Name, Description
tblDataSetB has two fields: Location, Country
<o
> </o
>
The first row of the SQL statement will be: Insert Into ( Name, Description )<o
></o
>
Now we have to ‘tell’ which values we want to insert.<o
></o
>
In this example the named range DataSetA has headers named Name and Description. It is not necessary to have the same names in your range as in your Access table, as long as you keep the same order. <o
></o
>
So the next line would be: Select Name, Description From DataSetA. Make sure you understand this. If the named range had headers like, Q and Z, where Q is the column with Descriptions and Z holding the Names. You have to write it like: Select Z, Q From DataSetA to get the right column into the desired field. Are you sure you understand this part?<o
></o
>
Ok, the last part of the SQL statement tells where to find the data, we use the IN operator to point to the Excel file and tell it is Excel, it will look like: <o
></o
>
In '" yourExcelFilePathAndName "' 'Excel 8.0;' <o
></o
>
<o
> </o
>
These are the basics to understand how the SQL is written.
One last thing, the principle used is that we are working in Access and pull data from Excel, and not working in Excel and pushing data to Access.<o
></o
>
This means you’ll have to create a module in Access. To run the code other than from the module, create a form with a command button. The problem is I don’t know your level of knowledge on this.<o
></o
>
<o
> </o
>
<o
>Here are the SQL functions:</o
>
<o
>
Code:
<o:p>Public Function SQL_WriteDataSetA(ByVal sFilename As String) As String</o:p>
<o:p>'Pull data from range DataSetA in Excel and insert into tblDataSetA
SQL_WriteDataSetA = "Insert Into tblDataSetA ( Name, Description ) " _
& "Select Name, Description From DataSetA " _
& "In '" & sFilename & "' 'Excel 8.0;'"</o:p>
<o:p>End Function
Public Function SQL_WriteDataSetB(ByVal sFilename As String) As String</o:p>
<o:p>'Pull data from range DataSetB in Excel and insert into tblDataSetB
SQL_WriteDataSetB = "Insert Into tblDataSetB ( Location, Country ) " _
& "Select Location, Country From DataSetB " _
& "In '" & sFilename & "' 'Excel 8.0;'"</o:p>
<o:p>End Function </o:p>
</o
>
The 'Native' version:
Code:
Public Sub PullDataFromExcel()
[COLOR=seagreen]'This is the code for your commandbutton[/COLOR]
Dim sYourSheet As String 'The full path and name of your Excelsheet
sYourSheet = [B][COLOR=darkred]"D:\YourFolder\YourFile.xls"[/COLOR][/B]
DoCmd.SetWarnings False
'Execute the SQL statments
DoCmd.RunSQL (SQL_WriteDataSetA(sYourSheet))
DoCmd.RunSQL (SQL_WriteDataSetB(sYourSheet))
DoCmd.SetWarnings True
End Sub
And the ADO version:
Code:
Public Sub PullDataFromExcelADO()
[COLOR=seagreen]'The ADO variant needs a reference to the:
'Microsoft ActiveX Data Object 2.x library[/COLOR]
Dim oCn As New ADODB.Connection
Dim sYourSheet As String 'The full path and name of your Excelsheet
sYourSheet = [COLOR=darkred][B]"D:\YourFolder\YourFile.xls"
[/B][/COLOR]Set oCn = CurrentProject.Connection
With oCn
.Execute (SQL_WriteDataSetA(sYourSheet))
.Execute (SQL_WriteDataSetB(sYourSheet))
End With
End Sub
Maybe this is not complete. Why?<o
></o
>
If you run the code, it will insert data to the two tables. Over and over again. So maybe you only want to insert new or updated records. You could simply delete all data first before doing the insert, but most of the time this is not the way to go.<o
></o
>
This is where you could use temporary tables to write to from Excel and from there on updated or insert to the desired tables using the right SQL statement. But for now I guess you have enough info to get started.<o
></o
>
