Solved

Excel modifications

Posted on 2006-10-31
7
1,012 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now