Access VBA to Excel and Entering Data into range

Question

I have the following code within an application that creates quotes for my customer.  I open a pre-saved template named QUOTE SKELETON.xlsx, insert some data from Access, save in a new file in a new folder and then close everything out:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
With xlApp
    Set xlWB = .Workbooks.Open("C:\QUOTE SKELETON.xlsx", , False) **this is a pre-saved template**
    Set xlSheet = xlWB.Worksheets("Quote")  **worksheet within template**
    
    'Transfer an array to the worksheet starting at cell A7
    xlSheet.Range("A7").Resize(93, 7).Value = DataArray

    'Insert Model # and Date
    xlSheet.Range("B3").Select
    Selection.Value = txtModel

    xlSheet.Range("C3").Select
    Selection.Value = Now()
End With

'Save the Workbook and Quit Excel
Call btnNewFolder_Click(strInput, strQuoteNo) **this is a sub that creates a new folder and new file name based on user input**

xlSheet.Range("A3").Select
Selection.Value = strQuoteNo **insert quote #

On Error Resume Next

xlWB.SaveAs strInput **save quote as new file in new folder**
xlWB.Close (False)
xlApp.Quit

The problem is that I can run the code the first time and it works great.  Subsequent runs (without closing my source form and Access) produces "Run-time error '91' Object variable or With block variable not set" when the code tries to insert the Model # into Excel (Selection.Value = txtModel).

Can someone tell me what I'm missing?

Answer 1

Hi,

since you are using Excel Automation and are working from Access, you cannot directly use Selection object. Like you cannot use just Range, you have to sue xlSheet.Range instead. Francly, there is no need in Selection at all. Just use:

'Insert Model # and Date
    xlSheet.Range("B3").Value = txtModel

xlSheet.Range("C3").Value = Now()

xlSheet.Range("A3").Value = strQuoteNo **insert quote #

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru

Answer 2

Hello,

This worked!! Thanks!