MartyConnelly
martyconnelly at shaw.ca
Sat Sep 10 12:50:00 CDT 2005
Here are a couple of rough sample methods to import and export via ADO
or DAO
from Excel and Access.
So you could do in VBA code
You can specify the sheet or even the invididual cell range.
You will have to fiddle around whether you want cell headers used in tables.
In certain cases the sheet name may or maynot have to be preceeded by a "$"
The reference to Excel 8.0 refers to the data format type of excel not
the Version
which hasn't changed since Excel 97, unlike Access.
In most cases you have to import to a fresh table in access
and I believe will overwrite the Sheet name on export to to excel
Sub test()
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Set oRS = New ADODB.Recordset
Set oConn = New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = "Excel 8.0"
.Open "C:\Excel\ExcelADO\Results\Products.xls"
'....
'Set oRS = cConn.Open("Select * from [Sheet1$A1:B10]", oConn,
adOpenStatic)
With oRS
.Open "Insert Into tbltest SELECT * FROM [Products$A1:B10]", _
oConn, adOpenStatic
'.Open "SELECT * FROM [Sheet1$] WHERE LastName = 'Doe'", _
' cnnDB, adOpenDynamic, , adCmdText
Debug.Print oRS.RecordCount
.Close
End With
.Close
End With
End Sub
Sub test2()
'sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel
8.0;DATABASE=E:\MyDocuments\Test.xls;HDR=Yes;IMEX=1].[Sheet1$]"
End Sub
Sub ImportExcelToAccessAll()
Dim cnn As New ADODB.Connection
Dim sqlString As String
Dim strmdbpath As String
Dim strexcelpath As String
Dim dbs As Database
Set dbs = CurrentDb
strmdbpath = "C:\Excel\ExcelADO\testADO range.mdb"
strexcelpath = "C:\Excel\ExcelADO\Results\Products.xls"
'Assumes Access table does not already exist
sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel 8.0;DATABASE=" & _
strexcelpath & ";HDR=Yes;IMEX=1].[Products$A1:B100]"
'Assumes Access table exists
'sqlString = "INSERT INTO [tblExcelNew] (Product, UnitPrice,
UnitsInStock)" & _
' " SELECT Product, UnitPrice, UnitsInStock from" & _
' "[Excel 8.0;DATABASE=" & strexcelpath & ";HDR=Yes;IMEX=1].[Products$];"
Debug.Print sqlString
dbs.Execute sqlString
Set cnn = Nothing
End Sub
Sub ImportExcelToAccess()
Dim sqlString As String
Dim strexcelpath As String
Dim dbs As Database
Set dbs = CurrentDb
strexcelpath = "C:\Excel\ExcelADO\Results\Products.xls"
'Import to Excel
'Assumes Access table does not already exist
sqlString = "SELECT * INTO [tblExcelNew] FROM [Excel 8.0;DATABASE=" & _
strexcelpath & ";HDR=Yes;IMEX=1].[Products$A1:B100]"
'export to Excel
sqlString = " INSERT INTO [Excel
8.0;Database=C:\temp\MyWorkbook.xls;].[MySheet1$E6:G65536]" & _
"SELECT * FROM MyTable WHERE MyKeyCol = 99"
'Assumes Access table exists
'sqlString = "INSERT INTO [tblExcelNew] (Product, UnitPrice,
UnitsInStock)" & _
' " SELECT Product, UnitPrice, UnitsInStock from" & _
' "[Excel 8.0;DATABASE=" & strexcelpath & ";HDR=Yes;IMEX=1].[Products$];"
Debug.Print sqlString
dbs.Execute sqlString
Set cnn = Nothing
End Sub
Rocky Smolin - Beach Access Software wrote:
>Well, it's just a little division up near Portland that's developing NICs.
>But still...
>
>Rocky
>
>----- Original Message -----
>From: "John W. Colby" <jwcolby at colbyconsulting.com>
>To: "'Access Developers discussion and problem solving'"
><accessd at databaseadvisors.com>
>Sent: Saturday, September 10, 2005 9:20 AM
>Subject: Re: [AccessD] Importing Multple Excel Worksheets
>
>
>
>
>>>What they're doing, just BTW, is taking a snapshot from the mainframe
>>>
>>>
>>system and importing it into E-Z-MRP for their weekly planning.
>>
>>8-) Intel using your software!
>>
>>John W. Colby
>>www.ColbyConsulting.com
>>
>>Contribute your unused CPU cycles to a good cause:
>>http://folding.stanford.edu/
>>
>>-----Original Message-----
>>From: accessd-bounces at databaseadvisors.com
>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
>>Beach Access Software
>>Sent: Saturday, September 10, 2005 12:02 PM
>>To: Access Developers discussion and problem solving
>>Subject: Re: [AccessD] Importing Multple Excel Worksheets
>>
>>
>>John:
>>
>>Yeah - already came across a sheet with a leading blank. But I can get
>>them
>>
>>to standardize. It's a division of Intel and they're pretty sharp.
>>
>>I'll probably go with your automation template (usually do) and link
>>directly to the spreadsheet so they don't have to do the import wizard 6
>>times before they run the delete and append queries.
>>
>>What they're doing, just BTW, is taking a snapshot from the mainframe
>>system
>>
>>and importing it into E-Z-MRP for their weekly planning.
>>
>>Thanks and regards,
>>
>>Rocky Smolin
>>Beach Access Software
>>http://www.e-z-mrp.com
>>858-259-4334
>>
>>
>>----- Original Message -----
>>From: "John W. Colby" <jwcolby at colbyconsulting.com>
>>To: "'Access Developers discussion and problem solving'"
>><accessd at databaseadvisors.com>
>>Sent: Saturday, September 10, 2005 8:36 AM
>>Subject: Re: [AccessD] Importing Multple Excel Worksheets
>>
>>
>>
>>
>>>One of the things you need to be careful of is the naming of the
>>>sheets inside of the workbook. Often times the sheet name is whatever
>>>the person exporting the data INTO excel wants to call it that day.
>>>Even if the export to Excel is automated, the designer may do
>>>something cute like name the sheet with date and/or time info. When
>>>you set up the link, the sheet name
>>>gets pulled into the link information. If the sheet name changes, the
>>>link
>>>fails.
>>>
>>>This can be worked around if the sheets are always in the same order,
>>>then you use automation to open the work book, then go through the
>>>sheets setting sheet one, two, three etc. to some fixed name YOU
>>>decide (such as Sheet1, or
>>>CustomerData etc). Close the workbook and now all of your sheets match
>>>what
>>>the link expects and everything is fine.
>>>
>>>John W. Colby
>>>www.ColbyConsulting.com
>>>
>>>Contribute your unused CPU cycles to a good cause:
>>>http://folding.stanford.edu/
>>>
>>>-----Original Message-----
>>>From: accessd-bounces at databaseadvisors.com
>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky
>>>Smolin - Beach Access Software
>>>Sent: Saturday, September 10, 2005 11:18 AM
>>>To: Access Developers discussion and problem solving
>>>Subject: Re: [AccessD] Importing Multple Excel Worksheets
>>>
>>>
>>>D'oh. Completely missed that. Thanks. This first test I'm just
>>>going to jam their data into an mdb with some queries. Ultimately, I
>>>think I should use automation, though, so they can have a one-button
>>>solution.
>>>
>>>
>>>Thanks,
>>>
>>>Rocky
>>>
>>>----- Original Message -----
>>>From: "Susan Harkins" <ssharkins at bellsouth.net>
>>>To: "'Access Developers discussion and problem solving'"
>>><accessd at databaseadvisors.com>
>>>Sent: Saturday, September 10, 2005 8:04 AM
>>>Subject: Re: [AccessD] Importing Multple Excel Worksheets
>>>
>>>
>>>
>>>
>>>>I need to convert client data on a regular basis from an Excel
>>>>workbook containing five worksheets. I need to link or import the
>>>>sheets separately, of course, but neither the link wizard nor the
>>>>import wizard seem to allow you to specify the worksheet.
>>>>
>>>>=========The Import wizard's very first pane should show two options:
>>>>Show Worksheets and Show Ranges. You're not getting this?
>>>>
>>>>Susan H.
>>>>
>>>>--
>>>>AccessD mailing list
>>>>AccessD at databaseadvisors.com
>>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>>Website: http://www.databaseadvisors.com
>>>>
>>>>
>>>>
>>>--
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>
>>>
>>>
>>>--
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>
>>>
>>>
>>--
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>>--
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada