Solved

Excel modifications

Posted on 2006-10-31
7
1,055 Views
Last Modified: 2008-01-09
on opening workbook getting error
"Unable to get the Open property of the Workbooks class"

DoCmd.OutputTo acOutputReport, "ConsIncGroup", acFormatXLS, "C:\Data\ConsIncGroup.xls", 0
            'DoCmd.OutputTo acOutputReport, "repIncomeStatement_LastYearsCons_Grouped", acFormatXLS, , -1
           
DoEvents

Dim mysheet As Object, xlApp As Object

Set xlApp = CreateObject("Excel.Application")
Set mysheet = xlApp.Workbooks.Open("C:\Data\ConsIncGroup.xls").Sheets(1)

mysheet.Cells(1, 1).Value = "Test"



mysheet.Application.ActiveWorkbook.Save
mysheet.Application.ActiveWorkbook.Close
xlApp.Quit

' Clear the object variable.
Set mysheet = Nothing
0
Comment
Question by:dmitryz6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17845478
Hey dmitry, how goes it?

Can you open the exported spreadsheet normally having exported it?
Does it perform any better if you skip the explicit Excel object creation (not ideal I know)...

------------------------------------------------------------------------------
DoCmd.OutputTo acOutputReport, "ConsIncGroup", acFormatXLS, "C:\Data\ConsIncGroup.xls", 0
            'DoCmd.OutputTo acOutputReport, "repIncomeStatement_LastYearsCons_Grouped", acFormatXLS, , -1
           
DoEvents

Dim mysheet As Object, mybook As Object, xlApp As Object

Set mybook = GetObject("C:\Data\ConsIncGroup.xls")
Set xlApp = mybook.Application
Set mysheet = mybook.Sheets(1)
xlApp.Visible = True
'etc etc
------------------------------------------------------------------------------


Or - if that doesn't help - perhaps giving up and making a file copy in code and opening that copy - deleting the "original" export when all's done.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17845489
   Set xlApp = CreateObject("Excel.Application")
    Set xlWb1 = xlApp.Workbooks.Open("C:\Test1.xls", True)
    Set xlWs1 = xlWb1.Worksheets("Sheet1")
0
 
LVL 19

Author Comment

by:dmitryz6
ID: 17845759
Leigh.

OutputTo giving error wit sheet name.
I will play and will be back tomorrow.

(Today I am going to pickup candies)

Thank you
  Dmitry
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17845922
are you sure there is Sheet1 in the excel file?
0
 
LVL 19

Author Comment

by:dmitryz6
ID: 17850172
Report caption property has “G/L Income statement”

When access outputting report to excel it create sheet ,with report caption property as sheet name. To have “/” in sheet name not valid. Excel trying to recover it self and it create problem with opening and putting changes. I started with Leigh suggestion to open excel and it helped.

Thank you everybody for participation
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17850470
Funny the things we miss at times huh?
Glad you're sorted Dmitry.  See you answering around EE again some time yes?
0
 
LVL 19

Author Comment

by:dmitryz6
ID: 17850777
Partner.

Thank you for help.
I am trying maintain my premium membership by answering just enough. Hope to see you around EE as well.

Regards
  Dmitry
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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