[RESOLVED] RESOLVED: Runtime error 1004 when exporting to excel

Hi, my application is getting the followed error

Runtime error 1004: Application defined or Object defined error

and this is the offending piece of code.

myBook.Worksheets(1).Range(bCol).Value = vDate

vDate is a string, and I have checked bCol as well to make sure that they have correct variables.

Any help much appreciated...

Post more code than that. It doesn't sound like Excel is being opened correctly.

What is the format of the column where that data is going?

Post the values of your variables too.

Moved from Classic VB.

Yes, excel is doing something strange, it does not seem to be closing even after my application has quit.

the runtime error problem I fixed by changing vDate to a type String but the problem seems to be with the spreadsheet either not opening or closing properly. If I close out of Vb and then try to open the spreadsheet I get a message that a user (me!) has it open and it can only be opened readonly so its like it has not closed properly.


how I have declared my Excel workbook:

Dim myBook As Excel.Workbook

I open it like this:

fname = exePath & "exportXL.xls"
Set myBook = Workbooks.Open(FileName:=fname)

When I have finished writing the data to the spreadsheet I close it like this:

myBook.Save
myBook.Close
Set myBook = Nothing

Hope this provides a few more clues.

Will you try it like this...

VB Code:

Public Sub Test()
Dim exl         As Excel.Application
Dim myBook      As Excel.Workbook
Dim fName       As String
 
    Set exl = New Excel.Application
    fName = exePath & "exportXL.xls"
    Set myBook = exl.Workbooks.Open(fName)
    'stuff
    myBook.Save
    myBook.Close
    Set myBook = Nothing
    exl.Quit
    Set exl = Nothing
End Sub

Using Dee-u's code will work or specifically, this line of code is causing excel to remain open as your using the default Workbooks object without referencing an object variable set to it first.

VB Code:

Set myBook = [b]Workbooks[/b].Open(FileName:=fname)
'Better to do...
Dim oApp As Excel.Application
Set oApp = New Excel.Application
Set myBook = oApp.Workbooks.Open(FileName:=fname)

Hey thanks for the help guys. Appreciate it, seems to have cleared up my problem

Glad it worked out.

Ps, as a new member I'll let you know that when you have your thread answered its courteous to mark your thread as Resolved so other members will know its solved. ;)

Thanks for the heads up

We are glad to be of help. :)