Link to excel using VBA

  • Thread starter Thread starter Michael S. Montoya
  • Start date Start date
M

Michael S. Montoya

I have to import various excel spreadsheets into Access.

The Filename of each xls file is the name of the event. I can use a Open
File dialog box to retrieve the file name, but the next step is where I get
stumped.

Each xls file contains many sheets. Each sheet is a date of the event which
I would like to retrieve and I need to loop through all the sheets and
retrieve the data.

After getting the file name, how can I use VBA to retrieve the name of each
sheet and loop through them all?
 
You'll need to use Automation to do that. Here's some info posted by John
Nurick in another thread:
--------------------
If you're new to Automation, here's a useful article giving the
basics of controlling Excel from Access:

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/default.aspx?scid=KB;en-us;123859

ACC2000: Using Automation to Create and Manipulate an Excel
Workbook (Q210148)
http://support.microsoft.com/default.aspx?scid=kb;en-us;210148

ACC: Using Automation to Create and Manipulate an Excel
Workbook (Q142476)
http://support.microsoft.com/default.aspx?scid=kb;en-us;142476

Recent versions of Excel have a Range.CopyFromRecordSet method
that can be very handy for importing data from Access.


Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like):

Dim intColumn As Integer
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , True
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset)
Do While xlc.Value <> ""
rst.AddNew
For intColumn = 0 To rst.Fields.Count - 1
rst.Fields(intColumn).Value = xlc.Offset(0, intColumn).Value
Next intColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing
 
Hi Michael,

Even though you are trying to do things from the Access world you need to
first solve the Excel issues in the Excel world and then bring the VBA from
Excel to Access. You can probably get definitive answers in
microsoft.public.excel.programming.

That being said, you can probably step through the sheets collection of the
current Excel workbook getting the .Name value for each sheet.

HTH
 
Back
Top