VB code to export data to Excel

[ChrisCione]

I have placed a command button on my form in MS Access to export specific data to populate specific cells in Excel. I won't be pulling ALL the data from my main table, just specific fields, and auto-populating specific cells in an Excel document (template) that I have created.

I don't know how to write code at all, but I found this on the web:

Dim appExcel As Excel.Application
  Dim wbk As Excel.Workbook
  Dim wks As Excel.Worksheet
  Set appExcel = GetObject("Excel.Application")
  Set wbk = appExcel.Workbook.Open("L:\Bper Spec\Cione\TEST\TEST (RSA).xls", , True)
  Set wks = wbk.Worksheet("RSA")
With xlSheet
.Cells(1, 1).Value = "I am in Row 1, Column A"
.Cells(3, 3).Value = "I am in Row 3, Column C"
'and so on, filling in the cells
End With

When I try to run the code, I get a compile error "Variable not defined" at the 5th line from the bottom "With xlSheet"

I've researched and researched to find a solution, but no dice.

Any help would be appreciated.

[Ron Weller]

with xlSheet should be with wks

[ChrisCione]

Tried it; got a runtime error.

[Ron Weller]

Changes in red - New code should look like this:

  Dim appExcel As Excel.Application
  Dim wbk As Excel.Workbook
  Dim wks As Excel.Worksheet

  Set appExcel = GetObject("Excel.Application")
  Set wbk = appExcel.Workbooks.Open("L:\Bper Spec\Cione\TEST\TEST (RSA).xls", , True)
  Set wks = wbk.Worksheets("RSA")
  With wks
    .Cells(1, 1).Value = "I am in Row 1, Column A"
    .Cells(3, 3).Value = "I am in Row 3, Column C"
    'and so on, filling in the cells
  End With

By the way: Even the corrected code will continue to error because you open the workbook Read Only

 Set wbk = appExcel.Workbooks.Open("L:\Bper Spec\Cione\TEST\TEST (RSA).xls", , True)

[Hack]

Originally Posted by ChrisCione

got a runtime error.

Ron's code will probably solve your problem for this, but I would suggest that in the future if something like this (an error) occurs that you specify what the error number is and what the error verbage is.

There are only about a zillion differnent error possibilities. Telling us specifically which error you are getting helps enormously.

Thanks.