Solved

Excel modifications

Posted on 2006-10-31
7
1,060 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

622 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