Populate Excel Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What seems to be a simple task, is driving me nuts. All I want to do is open
an existing Excel workbook and populate certain cells with values from my db.

I can't get the syntax for sheet names and cell addresses right.

Thanks in advance
 
Hi Rob

First set a reference to the "Microsoft Excel x.x Object Library"
(Tools>References from the code window)

Then you can use code like this:

Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlSht As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSht = xlWkb.Worksheets("Sheet1")
With xlSht
.Range("A1") = "Hello"
.Range("A2") = "World"
.Range("B10") = 9999
End With
xlWkb.Close SaveChanges:=True
xlApp.Quit
Set xlSht = Nothing
Set xlWkb = Nothing
Set xlApp = Nothing
 
Thank you very much Graham.

Graham Mandeno said:
Hi Rob

First set a reference to the "Microsoft Excel x.x Object Library"
(Tools>References from the code window)

Then you can use code like this:

Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlSht As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWkb = xlApp.Workbooks.Open("C:\Test.xls")
Set xlSht = xlWkb.Worksheets("Sheet1")
With xlSht
.Range("A1") = "Hello"
.Range("A2") = "World"
.Range("B10") = 9999
End With
xlWkb.Close SaveChanges:=True
xlApp.Quit
Set xlSht = Nothing
Set xlWkb = Nothing
Set xlApp = Nothing

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Rob LMS said:
What seems to be a simple task, is driving me nuts. All I want to do is
open
an existing Excel workbook and populate certain cells with values from my
db.

I can't get the syntax for sheet names and cell addresses right.

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top