Export a Named Range from Excel to Access… (2000 SR-1)

Topic

New Reply

Hi all…

I’m looking for code to export a named range directly from Excel to an Access 97 database…
The reason I want to do it this way, rather than import from inside Access, is that I want it automated… and there is a new excel file created every day… with difference names… The database that the data is going to is always going to be in the same location and exported into the same table…

Specifically… in VBA, I want to say:

Export the named range, “Prices”, from this workbook… to “ThePathandDBName.mdb”, table named Prices… Yes, the first row contains headings… and Yes, overwrite the existing table…

Can this be done?
Thanks in advance!

Reply | Quote

Replies

Trudi

Please take a look at this message from Rory , and the whole thread if you like.

If you have any additional questions, please post a reply.

Wassim

Reply | Quote

Wassim (and Trudi),
The code in my post may need revision if the named range contains more than one row (which I imagine it does). The For Each rngCell loop may not go through the range in the order required! (I confess I haven’t tested it.) It would be safer to use row and column variables and loop through that way.
Hope that helps.

Reply | Quote

Thanks Rory… I saw that it would need some revision… I’ve done the For Next loop in Excel VBA for other situations…
I always appreciate the guidance!

Have a great weekend!

Reply | Quote

Thanks Wassim… I chose Hans’ automation suggestion, simply because the named range has quite a few fields and I’m feeling lazy today… …but thanks for the alternative… I am sure I will use it at another time!

Reply | Quote

An alternative would be to use Automation to start Access from Excel. You need to set a reference to the Microsoft Access 9.0 Object Library in Tools | References…

Sub ExportPrices()
Dim appAccess As New Access.Application
Dim strRange As String
Dim strDatabase As String
Dim strTable As String

On Error GoTo ErrHandler

‘ Modify the following strings as needed
strRange = “Prices”
strDatabase = “serversharefoldermyDatabase.mdb”
strTable = “Prices”

appAccess.OpenCurrentDatabase strDatabase
On Error Resume Next
appAccess.DoCmd.DeleteObject acTable, strTable
On Error GoTo ErrHandler
appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, ActiveWorkbook.FullName, True, strRange

ExitHandler:
appAccess.Quit acQuitSaveNone
Set appAccess = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Thanks Hans…

I only have Access 97… so I had to reference the 8.0 Object Library… and in turn, change the “acSpreadsheetTypeExcel9” to “acSpreadsheetTypeExcel97″… but it worked perfectly! … I had planned to do it this way, but the filename part of the import statement had me stumped… I didn’t know about the FullName property of the ActiveWorkbook… I really need to get an Excel VBA book…

Thanks, once again, for teaching me something new…

Reply | Quote

I’m sorry, you did state that it was for Access 97, but I just looked at the 2000 in the subject. Glad you figured it out.

Reply | Quote