none
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?



    • Edited by HMCBill Thursday, February 28, 2013 4:05 PM To clarify problem
    Wednesday, February 27, 2013 11:59 PM

Answers

  • 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

    • Marked as answer by HMCBill Thursday, February 28, 2013 4:03 PM
    Thursday, February 28, 2013 6:55 AM

All replies

  • 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

    • Marked as answer by HMCBill Thursday, February 28, 2013 4:03 PM
    Thursday, February 28, 2013 6:55 AM
  • Hello,

    This worked!! Thanks!

    Thursday, February 28, 2013 4:03 PM