?
Solved

write data to excel from recordset

Posted on 2006-05-10
10
Medium Priority
?
221 Views
Last Modified: 2010-04-23
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?
0
Comment
Question by:SysCapstone
  • 4
  • 4
  • 2
10 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 16652672
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
0
 
LVL 7

Expert Comment

by:davidrichardson
ID: 16652686
when you save is the excell proces still running

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

Expert Comment

by:bruintje
ID: 16652711
made a typo there

worksheet = xlbook.Worksheets(1)
worksheet.Cells(1,1) = 'some variable here
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:SysCapstone
ID: 16655219
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.
0
 

Author Comment

by:SysCapstone
ID: 16661729
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!
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16661756
do you open it by hand?
0
 
LVL 44

Accepted Solution

by:
bruintje earned 2000 total points
ID: 16661788
could also be a a double worksheet, try

Dim worksheet As Excel.Worksheet
worksheet = xlApp.Sheets(startSheet)
0
 

Author Comment

by:SysCapstone
ID: 16661872
i tried that and it worked the first time, but hasnt since.
0
 
LVL 7

Expert Comment

by:davidrichardson
ID: 16662137
when you say "the excel sheet cannot be opened" do you get any error messages?
0
 

Author Comment

by:SysCapstone
ID: 16662940
thanks bruintje, its working now.  i just had to restart my computer.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question