Embedded Excel problem

I tried to adapt some code posted in earlier threads to export a set of 
embedded Excel worksheets and save them as separate Excel files. It runs fine 
when I step through by pressing F8 or even when I set an F9 breakpoint on the 
"SaveAs" line, but throws a run error 1004 (the SaveAs method of the 
'_Workbook' object failed) on the first For loop (at the SaveAs line) when i 
try to run it normally using F5.
Can anyone see what's going wrong?
Thanks

Sub ExportEmbeddedSheetsAsExcel()
    
    Dim iCtr As Integer
    Dim xlWB As Excel.Workbook
    Dim oDoc As Document
    Dim oDcOle As Word.OLEFormat
    Dim strDocName As String

    strDocName = ActiveDocument.FullName
    Set oDoc = ActiveDocument
    For iCtr = 1 To oDoc.InlineShapes.Count
        If oDoc.InlineShapes(iCtr).Type = wdInlineShapeEmbeddedOLEObject Then
            If oDoc.InlineShapes(iCtr).OLEFormat.ClassType = "Excel.Sheet.8" 
Then
                Set oDcOle = oDoc.InlineShapes(iCtr).OLEFormat
                oDcOle.DoVerb wdOLEVerbPrimary
                Set xlWB = oDcOle.Object
                intPos = InStrRev(strDocName, ".")
                strDocName = Left(strDocName, intPos - 2)
                strDocName = strDocName & iCtr & ".xls"
                xlWB.SaveAs FileName:=strDocName
                xlWB.Close
            End If
        End If
    Next iCtr
    
    Set xlWB = Nothing
    Set oDoc = Nothing
    Set oDcOle = Nothing
End Sub
0
Utf
4/16/2010 5:13:01 PM
word.vba.general 1023 articles. 1 followers. Follow

1 Replies
1699 Views

Similar Articles

[PageSpeed] 24

I found the answer myself.
Replace this line:
oDcOle.DoVerb wdOLEVerbPrimary
By this one:
oDcOle.DoVerb VerbIndex:=1

If not, the embedded Excel doesn't get "opened" in Excel in the first For 
loop.

"David Turner" wrote:

> I tried to adapt some code posted in earlier threads to export a set of 
> embedded Excel worksheets and save them as separate Excel files. It runs fine 
> when I step through by pressing F8 or even when I set an F9 breakpoint on the 
> "SaveAs" line, but throws a run error 1004 (the SaveAs method of the 
> '_Workbook' object failed) on the first For loop (at the SaveAs line) when i 
> try to run it normally using F5.
> Can anyone see what's going wrong?
> Thanks
> 
> Sub ExportEmbeddedSheetsAsExcel()
>     
>     Dim iCtr As Integer
>     Dim xlWB As Excel.Workbook
>     Dim oDoc As Document
>     Dim oDcOle As Word.OLEFormat
>     Dim strDocName As String
> 
>     strDocName = ActiveDocument.FullName
>     Set oDoc = ActiveDocument
>     For iCtr = 1 To oDoc.InlineShapes.Count
>         If oDoc.InlineShapes(iCtr).Type = wdInlineShapeEmbeddedOLEObject Then
>             If oDoc.InlineShapes(iCtr).OLEFormat.ClassType = "Excel.Sheet.8" 
> Then
>                 Set oDcOle = oDoc.InlineShapes(iCtr).OLEFormat
>                 oDcOle.DoVerb wdOLEVerbPrimary
>                 Set xlWB = oDcOle.Object
>                 intPos = InStrRev(strDocName, ".")
>                 strDocName = Left(strDocName, intPos - 2)
>                 strDocName = strDocName & iCtr & ".xls"
>                 xlWB.SaveAs FileName:=strDocName
>                 xlWB.Close
>             End If
>         End If
>     Next iCtr
>     
>     Set xlWB = Nothing
>     Set oDoc = Nothing
>     Set oDcOle = Nothing
> End Sub
0
Utf
4/17/2010 6:31:01 AM
Reply:

Similar Artilces:

Extracting embedded photos
Not sure this is a conversion issue, but don't know where to post it. Before I learned the hard way, I embedded photos in a database, linked to another database for display. I've reached the database size limit and realize I should have linked the photos instead. (original photos not available) Any ideas on a process to run thru the embedded photos and convert them back to original jpg - the manual process is too massive an effort. Thanks See http://www.lebans.com/oletodisk.htm On Thu, 13 Apr 2006 19:18:01 -0700, bigitch <bigitch@discussions.microsoft.com> wrote: >Not ...

windows excel to mac excel
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Help! <br><br>having transferred my old windows excel files to my new mac there seems to be a major compatibility issue when opening the files. <br><br>The page settings are completely different and all photos appear stretched wider and compressed in height. <br><br>Is there a way to view windows created excel files with the same page settings as they were originally created without this stretch and squash effect? ...

How to merge two Excel Files. #2
im using Office XP, and i have some data in 4to5 different files, i want ot merge all that files, can anybody help me.... thax in advance. Brijesh Parmar --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003 The big question is whether your files have "similar or the exact same (hopefully) layout. If the latter, I'd just create a new workbook and copy and paste each of your 5 to the new master <<a simplified approach>>. "Brijesh" <parmarb...

embedded database
We are developing software applications for Insurance agents that will reside on their laptops and desktops. We are looking for recommendation on which client side embedded database to use in our application. I was wondering if anyone knows what Intuit uses in their product or what Microsoft uses for MS Money. Any recommendations would be greatly appreciated. Thank you. Paul Money uses MSISAM. Little is known about this--it apparently is a closed-up version of the Jet database used by MS Access. "P Gupta" <pkgupta@yahoo.com> wrote in message news:qd7qc.318$eQ3.177@newss...

Help
I have a monthly process where I bring a range of cells into a spreadsheet (copy-paste) and I need the information to be numbers but it is created (out of my control) as text such as... '1.23 '1.24 '1.25 and so on for about a hundred rows It is really cumbersome to have to keep pressing F2 to edit, home to get to the ' and then delete and enter to remove the ' mark and convert the text to a number I used the Macro recorder (with relative value set) but it picks up the absolute value of the first cell and then when I run the macro it puts that value into each cell What I...

Problem with Auto fill
I am using Excel 2003 and W XP. I have the height of my rows at 12.75. Some rows are larger because data in a cell on those rows needs 2 lines. When I am entering data in a row that is only 12.75 high and I type a couple of characters Auto Fill kicks in and shows me the entire contents for the cell. I then hit Enter and Auto Fill completes. The problem is that even though the data is only 1 line in length the height of the row is doubled to 25.5. I do not want this to happen, how can I stop it? This also happens if the data I want is in the cell above mine and that cell is only 12.75...

excel money addin should have time series data
can i get time series data from msn money excel addin?? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=bf0b8395-dfc4-42da-87d6-2c9ce55f315c&dg=microsoft.public.excel.misc Can you be more specific? D...

Excel 2007 Macro Recorder not recording shapes
Hello, I am trying to figure out why my Excel macro recorder is not recording creation of any shapes or changes to existing shapes. I started the macro, and drew a rectangle and stopped it. This is all the code I get. I like to see the syntax for shapes and I am unable to see maybe there is some problem with macro recorder. Please help! Thanks! Sub Macro1() ' ' Macro3 Macro ' ' ActiveWindow.SmallScroll Down:=25 End Sub Regards, Jaan On Feb 14, 2:35=A0pm, jaanbazz <jaanb...@domain-xyz.in> wrote: > Hello, > I am trying to figure out why...

Macros in Excel #5
How can i create a macro that changes what is in a drop down box by what is selected in another dropdown box? You could record a macro of your changing the validation list. However, you can do it without a macro using the technique here: http://contextures.com/xlDataVal02.html In article <558CC81E-4F2C-4A4C-B9A6-0B8E533722D2@microsoft.com>, "alhawtin" <alhawtin@discussions.microsoft.com> wrote: > How can i create a macro that changes what is in a drop down box by what is > selected in another dropdown box? ...

embedded manifest
How can modify the embedded manifet for MFC Standard DLL and MFC Application to set: <requestedExecutionLevel level="asInvoker|highestAvailable|requireAdministrator" uiAccess="true|false"/> Hi Don ...

Subtotal In Excel 2007 Table
How do I use the Subtotal function with a Excel 2007 Table and not convert it back to a range? TIA ...

Exciter: Custom Excel Applications!
If anyone is interested in a new site offering custom-built excel macros for every use, please visit www.exciter.gr. We offer good quality tools, priced by the project and not by the hour. Please visit www.exciter.gr to download demos and learn more. ...

how do i make a CVS file from an excel sheet
i have names and numbers in an excel sheet and want to save them as a CSV format so i can export them to a software i have instead of adding them one by one to the software but when i save on excel as CSV it says (this file may contain features that are not compatible with CSV (Comma delikited).... Hi, Just a thought ... Try copy all cells and then paste into notepad. or save as a text file - Mark >-----Original Message----- >i have names and numbers in an excel sheet and want to save them as a CSV >format so i can export them to a software i have instead of adding them on...

Can Excel do this?
I have exported +7000 Tracks and Artists from a program. I want to print a book which would read/look better if I could hide all after the first instance of the Artist. i.e.: Abba Dancing Queen Take A Chance on Me Waterloo Aerosmith Billy's Got a Gun Love in an Elevator Sweet Emotion etc. Could Excel hide any instance after the first of the Artist or delete it. (Obviously if deleted I would need to maintain the original file for sorting purposes) I would import the sorted Excel file(s) into a p...

Converting Excel 97files to Excel 95
I have Excel 95 but never need to use Excel, so I have no reason to upgrade. Occasionally I get an attachment in my email that is an Excel 97 file, but I can't open it with Excel 97. Is there a converter that goes backwards, from Excel 97 to Excel 95. Thanks, Jim Jim, There is no backwards converter. If you don't want to upgrade but do want to open and view later version files, try here........ http://www.microsoft.com/downloads/details.aspx?FamilyID=4EB83149-91DA-4110-8595-4A960D3E1C7C&displaylang=EN John "jim hinds" <jjhinds@wt.net> wrote in message ...

Embedding a CRichEditView in a CDialogBar
Anyone ever done this successfully before. I create the dialog with a richeditview using the resource editor VC6 and VC2003. Program compiles and links okay, program exits when it fails to create the dialog bar. Regards, Josh I think you need to call AfxInitRichEdit/AfxInitRichEdit2. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Josh Washington" <joshwashington@bigpond.com> wrote in message news:c8ZVb.49861$Wa.16696@news-server.bigpond.net.au... | Anyone ever done this successfully before. | I create the dialog with a richeditview using the resource editor VC6 and | V...

Why is it that the photos I insert are Embedded?
I used to be able to "Insert Picture From File" and actually see my photo. Now when I perform the same action I can resize my photo but but I cannot see it!? The Graphics Manager Task pane show all my inserts (photos & clip art alike) are embedded. I do not want them embedded. How can I stop them from being embedded?....I don't know why this is happening, it never used to...could some update have caused this? I use Publisher 3003. THANKS FOR ANY HELP IN THIS MATTER! Teresa K wrote: > I used to be able to "Insert Picture From File" and actually see my ...

Excel
I just had an excel upgrade and can't find the way to add arrows to a cell. I old excel the result was to up and down arrows, at the side of the cell, which would allow the user to click up or down to change the cell value... Insert a Spin Button from Forms Toolbar or Control Toolbox. Assuming upgrade means 2007 version....... Click on Developer Tab and in Controls category Insert>Form Controls. Select the spin button and place it where you want. Right-click and Format Control. Pick a cell to link. If you don't see Developer Tab on Ribben go to Button>Ex...

How do i create a running total in Excel?
Hi Can anyone help me: I wish to create a very simple to use goods in/ out spreadsheet. Each row will be a different part number. Column A = Part Number Column B = Amount of Goods In Column C = Amount of goods out Column D = Current Stock Held What I want it to do is to add up the quantity each time goods arrive in one cell (cumulatively)and then do the opposite when goods go out. Then subtract Column c from Column B to give me a running total (Column D). Anyone know the formula? If I understand what you want... Excel formulas return results based on the numbers actually in the spr...

Excel Formulas look different
I received a spreadsheet of mine back from a coworker, and all the formulas now look something like =Totals!R[3]C[-1] instead of =TOTALS!C73. Anyone know why they changed and how to change them back? -- User13 The second sheet has R1C1 Reference Style enabled. Follow these steps to fix: If in Excel 2007: <Office Button (in top left corner of screen)> <Excel Options> Click on "Formulas" tab Un-select R1C1 reference style If in Excel 2003: <Tools> <Options> I think it's under General Tab (i think... can't remember exactly as i'...

How do I display two excel pages at the same time?
I want to have two excel pages displayed on my computer at the same time, but when I open both pages it uses the same master excel and when I toggle between the two it won't open both of them up. I'm using office 2003. Thanks for the help. -- snowtime ------------------------------------------------------------------------ snowtime's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25982 View this thread: http://www.excelforum.com/showthread.php?threadid=393499 Open both and then do windows>arrange and vertical (or any of the other choices, I usuall...

Embeded Links
I have embedded links in some files, but there are no names in the worksheet or formulas which are linked to other workbooks. Does anyone have a method of deleting these 'ghost' links. Hi try the following add-in to find/delete links: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink.zip -- Regards Frank Kabel Frankfurt, Germany "Brian" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0beb01c46e83$c3cc8c30$a501280a@phx.gbl... > I have embedded links in some files, but there are no > names in the worksheet or formulas which are linked to...

Excel dates Problem
What I am trying to do is return a certain value in a cell if the value of a different cell is within a one month range. I am currently trying to use the IF function, but it isn't working out. Ex. IF A1 is between 1/1/2004 and 1/31/2004 then return A2. --- Message posted from http://www.ExcelForum.com/ Please follow posting guidelines and post only in one group -- Regards, Peo Sjoblom "brett4098 >" <<brett4098.zm85b@excelforum-nospam.com> wrote in message news:brett4098.zm85b@excelforum-nospam.com... > What I am trying to do is return a certain value in ...

With EXCEL 2K,I need use autofilter while protecting sheet, how?
With EXCEL 2K,I need use autofilter function while protecting a sheet, how to make it happen? You can right click on the auto filter button and click Format cell Select protection tab and make sure it is NOT locked. Now when you protect the sheet it should allow the filter to work while still protected. Hope this does the trick. Jennifer "Song" wrote: > With EXCEL 2K,I need use autofilter function while protecting a sheet, how to > make it happen? Thanks, Jennifer, but the filter button doesn't work once you protect the sheet. I am using excel 2000, I am not sure i...

In Excel spreadsheet what does Ref # mean?
What does Ref # mean in Excel spreadsheet? #REF! is a error type which occurs when a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) is not valid. Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears. Review the possible causes and solutions (Taken from MSExcel help) -- Jacob "tjwhite1209" wrote: > What does Ref # mean in Excel spreadsheet? ...