Manipulating Excel via Access

  • Thread starter Thread starter Tony Wainwright
  • Start date Start date
T

Tony Wainwright

Hi Guys

I need to manipulate data within an Excel spreadsheet from within Access. I
have tried linking/importing the spreadsheet into Access but I am having
problems with data typing once linked/imported.

Is there anyway I can automatically manipulate data within Excel from Access
without linking or importing the spreadsheet?

I am using Access XP

Tony
 
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). This code opens an
EXCEL workbook and reads data from it and writes the data into a recordset.
You can modify this as needed to do things directly on the spreadsheet, etc.


Dim lngColumn As Long
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, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
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
 
I need to manipulate data within an Excel spreadsheet from within
Access.
Click to expand...

Have you thought of manipulating the Jet data from within Excel? It's
faster (OLE automation can really c r a w l ....) and much easier to
debug. All the normal ADO or DAO are there to use in exactly the same way
as Access.

If you need user intervention, then MSForms are not nearly as kind as
Access forms, but for me the advantage is still well over on the side of
keeping everything in one application.

All the best


Tim F
 
Back
Top