asked on

write data to excel from recordset

I am getting data from an Access db into a recordset then trying to write that data to specific cells in an excel sheet. i can read the data fine and i think it is writing correctly, but when the program ends the excel sheet cannot be opened if i have used:
xlBook.Save()

here is how i am opening the excel book:
Dim xlBook As Excel.Workbook
xlBook.GetObject(sSourceData) 'where sSourceData is the filename of the excel sheet to write to
Dim worksheet As Excel.Worksheet

worksheet.Cells(1,1) = 'some variable here

if i dont save i can still open the excel book but obviously it doesnt have the changes that i have made. if i use the save command, excel will open but the workbook that i have made changes to will not. any ideas?

Brian Mulder

Hello SysCapstone,

are you binding the worksheet to write the values to to the workbook?

Dim xlBook As Excel.Workbook
xlBook.GetObject(sSourceData) 'where sSourceData is the filename of the excel sheet to write to
Dim worksheet As Excel.Worksheet

like

xlbook.worksheets(1).Cells(1,1) = 'some variable here

or

worksheet = xlbook.Worksheets(1)
worksheet(1).Cells(1,1) = 'some variable here

the worksheet needs to be referenced through the workbook else you can't see where it is ending up\

hope this helps a bit
bruintje

davidrichardson

when you save is the excell proces still running

http://www.vbcity.com/forums/topic.asp?tid=76537

Brian Mulder

made a typo there

worksheet = xlbook.Worksheets(1)
worksheet.Cells(1,1) = 'some variable here

SysCapstone

ASKER

I actually worked that first part out by using and excel application not a workbook, but have a different related question...i want to write an entire access table into an excel sheet (basically copy it), but i want it to be added at the end of the existing data in that sheet.

SysCapstone

ASKER

i am still having big problems...this is how i am doing the excel thing now...

Dim xlApp As Excel.Application
xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open(filename:=sSourceData)
Dim worksheet As New Excel.Worksheet
worksheet = xlApp.Sheets(startSheet)

and i am closing it like this:

xlApp.ActiveWorkbook.Save()
xlApp.ActiveWorkbook.Close() 'this did not work on the excel file, but changes were actually made and saved, try renaming the file
xlApp.Quit()

'release the com objects
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing
GC.Collect()

but after i close out the program and go to open the excel file, the shell of excel will open but not the file. its not even a blank spreadsheet. HELP!

Brian Mulder

do you open it by hand?

ASKER CERTIFIED SOLUTION

Brian Mulder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SysCapstone

ASKER

i tried that and it worked the first time, but hasnt since.

davidrichardson

when you say "the excel sheet cannot be opened" do you get any error messages?

SysCapstone

ASKER

thanks bruintje, its working now. i just had to restart my computer.