Export unbound textbox to excel cell

[Sketchin]

I have found this code to export an unbound textbox to an excel spreadsheet cell but am recieving the error "Run-time error '429': ActiveX component can't create object".

Here is the code:

Private Sub Command294_Click()

    Dim XLApp As Excel.Application
    Dim wrkWorkbook As Excel.Workbook
    Dim wrkSheet As Excel.Worksheet
    
    Set XLApp = CreateObject("Excel.Application")
    XLApp.Workbooks.Open ("c:\damontest.xlsx")
    Set wrkWorkbook = XLApp.ActiveWorkbook
    Set wrkSheet = wrkWorkbook.ActiveSheet
    
    XLApp.Visible = False
    
    wrkSheet.Range("A1").Select
    Me.TxtTotalUsageToDateFiscalSME.SetFocus
    Selection = TxtTotalUsageToDateFiscalSME.text <-----FAILS HERE
    
    wrkWorkbook.Close True
    Set wrkSheet = Nothing
    Set wrkWorkbook = Nothing
    Set XLApp = Nothing
End Sub

I believe I have all the references required...any ideas?

 

[Trevor G]

When you are creating another application to open and want to work in it then you would have to use the Dim and work in the range or cell in this case using a with statement to save declaring each time look at this. To test this I would make it visible first then change the visible afterwards to false once you know it is working.

Dim xlApp As Excel.Application
Set xlApp = createobect("Excel.Application")
With xlApp
    .Visible = True
    .Workbooks.Open ("c:\damontest.xlsx")
    .Sheets("Sheet1").Select
    .Range("A1") = Me.TxtTotalUsageToDateFiscalSME.Text
    .ActiveWorkbook.Close True
    
End With
Set xlApp = Nothing

 

[boblarson]

And don't use .TEXT unless your code is on the On Change event of that particular control. Just use the default .VALUE.

.Range("A1") = Me.TxtTotalUsageToDateFiscalSME

And don't use SELECTION by itself. You don't need to select an Excel cell to populate or do anything with it. You also need to keep everything tied to an explicit object. Read here for more about that:
http://www.btabdevelopment.com/ts/excelinstance

 

[Sketchin]

Holy Sh&% it worked!!!

Thanks a million guys!

 

[Trevor G]

Happy to help and thanks for replying to let us know it worked for you.