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
Solved

Excel modifications

Posted on 2006-10-31
7
1,040 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

808 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