CODEWITHSUNDEEP
excelvbams-access
BoatHouse
BoatHouse

Reputation: 57

How to write to excel from vba

I am trying to create a button on my Access form that takes the current entry and writes the information to an Excel doc

Public Sub ExportExcel()
Dim ObjEx As Object
Dim WhatIsThisVariableFor As Object
Dim Selec As Object
Dim Column As Integer
Dim Row As Integer

Dim CustName As String
Dim Org As String
Dim Contact As String
Dim Product As String
Dim Quantity As Integer

Dim rst As DAO.Recordset

Set ObjEx = CreateObject("EXCEL.APPLICATION")
Set WhatIsThisVariableFor = ObjEx.Workbooks.Add

'Set rst = CurrentDb.OpenRecordset("Select") <---- This will be used when I get the code working
Column = 1
Row = 1
CustName = "Test" '<---- This is only used for the test

Cells(Row, Column) = CustName

ObjEx.Visible = True

Set Selec = ObjEx.Selection
End Sub

This code creates the Excel doc, but leaves it blank. I think that the Cells(Row, Column) command isn't working because it would have to be called from within excel? I'm not sure (I am very new to VBA)

How can I write to the spreadsheet cells from within Access?

Upvotes: 1

Views: 70

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You need to qualify your Cells() function to Excel's application object.

ObjEx.ActiveSheet.Cells(Row, Column).Value = CustName

I would recommend that you also choose the worksheet object explicitly:

Dim ws As object
Set ws = ObjEx.Worksheets("Sheet1")

ws.Cells(row, column).value = CustName

Upvotes: 3

Related Questions