Solved

Excel modifications

Posted on 2006-10-31
7
1,031 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

786 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