Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word to Excel
Hi,
Its been sometime since I posted my queries in this forum. New IT policy in the compnay, proxy net connection .. i dunno what, but Im not able to access NG's through Outlook and posting through Google is.... I have some data in word which has to be transfered to Excel automatically. Using Jon P's Excel to PPT automation examples I wrote the following code in Word. a) Problem is if I have some tables in Word then the table borders dont get transfered to excel. Is this normal? On the other hand if I manually copy paste from Word to Excel then do paste special and choose HTML, one can get borders etc same as Word. if I use the syntax -- oExcelWorkS.Range("a1").PasteSpecial Format:=wdPasteHTML, Link:=False, DisplayAsIcon:= _ False then I get Compile error: named argument not found in Format above. Why am I getting this error? What would be the correct synax? b) I think my Present code is equivalent to a paste special - text or unicode text (which one is it?) Whats the different between the 2? c) In word Iam using early binding to excel and when I write the code line -- oExcelWorkS.Range("a1").PasteSpecial -- and immediately after Pastespecial if I give a space then word prompts with some excel constants - xlpasteall, xlpasteformats --etc, but when I use any of these then I get a runtime error at this line (1004) -- paste special method of range class failed? Why is this happening Regards, Hari India Option Explicit Sub TransferDatatoExcel() 'Set the reference to Microsoft Excel 11.0 Object library (or 10.0/9.0 _ depending on your version of Office) in Tools - References Dim oExcel As Excel.Application Dim oExcelWorkB As Excel.Workbook Dim oExcelWorkS As Excel.Worksheet Dim fileName As Double Application.ScreenUpdating = False Selection.WholeStory Selection.Copy Set oExcel = CreateObject("excel.application") oExcel.Visible = msoTrue Set oExcelWorkB = oExcel.Workbooks.Add Set oExcelWorkS = oExcelWorkB.Worksheets("sheet1") oExcelWorkS.Range("a1").PasteSpecial xlPasteValues fileName = Now() With oExcelWorkB .SaveAs "d:\Time Stamp - " & fileName & ".xls" .Close End With oExcel.Quit Set oExcel = Nothing Set oExcelWorkB = Nothing Set oExcelWorkS = Nothing Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word to Excel
Hari:
a) Problem is if I have some tables in Word then the table borders dont get transfered to excel. Is this normal? On the other hand if I manually copy paste from Word to Excel then do paste special and choose HTML, one can get borders etc same as Word. if I use the syntax -- oExcelWorkS.Range("a1").PasteSpecial Format:=wdPasteHTML, Link:=False, DisplayAsIcon:= _ False then I get Compile error: named argument not found in Format above. Why am I getting this error? What would be the correct synax? The PasteSpecial method has different arguments depending on whether it's called from the Range object work the Worksheet object. The named argument 'Format' is only valid if called from the Worksheet object. oExcelWorkS.Range("A1").Select oExcelWorkS.PasteSpecial Format:="HTML" etc... This is one of the few times where you have to use Select. b) I think my Present code is equivalent to a paste special - text or unicode text (which one is it?) Whats the different between the 2? You're pasting values, which means that you're not pasting any formatting. The formatting of the cell that you paste in doesn't change and only the raw text that was copied is pasted. I don't know if that's text or unicode text, but I suspect what you're getting is some kind of default pasting. See my response to c. c) In word Iam using early binding to excel and when I write the code line -- oExcelWorkS.Range("a1").PasteSpecial -- and immediately after Pastespecial if I give a space then word prompts with some excel constants - xlpasteall, xlpasteformats --etc, but when I use any of these then I get a runtime error at this line (1004) -- paste special method of range class failed? Why is this happening I don't know for sure, but here's my guess. When you Paste Special, you can get different dialog boxes based on the source of the data in the clipboard. If you copy within Excel, the dialog gives you options like Values, Formats, Validation. These options mirror what you're getting in Intellisense (e.g. xlPasteValues, xlPasteFormats). If you copy to the clipboard from outside Excel, you get a different dialog box, one that asks you what format to paste the data. It seems that these two behaviors are also the difference between using the PasteSpecial method of the Range object vs. the Worksheet object. I think the problem that you're having is that your supplying an argument (xlPasteValues) when you need a format ("HTML"). The argument you're supplying matches the method that you're using, but they don't match the data that's in the clipboard. From within Excel, I think you can get away with this because Excel compensates. But from automation, it's a little more strict. The answer for you, I believe, is to select A1, then use the PasteSpecial method of the Worksheet object and supply the format. Don't supply wdPasteHTML though as that is an intrinsic constant in the Word object model and may not have the same meaning in Excel. Use "HTML", "Text", or "Unicode Text" as the argument. This seems to be what Excel produces when you record that paste operation. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word to Excel
Hi Dick,
Thnx for your great inputs. Especially the "This is one of the few times where you have to use Select." is a clincher argument. Would try out and post back if I have some problems. A related query. Actually the data we are copying comes from Adobe acrobat and it is being converted to Word format (as an intermediary) and then using the above macro converted to Excel. Is it possible for one to automate Adobe acrobat through excel directly (avoiding the intermediary Word) or is it possible to write such VBA based macros in adobe acrobat (does adobe have a automation feature?) which would directly transfer the data to excel. One of my colleagues is actually working on the adobe data and he asked if Word to excel is possible. hence, the above macros. If Adobe to excel directly is possible then word would not be used as an intermediary. Regards, hari India |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word to Excel
Is it possible for one to automate Adobe acrobat through excel directly (avoiding the intermediary Word) or is it possible to write such VBA based macros in adobe acrobat (does adobe have a automation feature?) which would directly transfer the data to excel. One of my colleagues is actually working on the adobe data and he asked if Word to excel is possible. hence, the above macros. If Adobe to excel directly is possible then word would not be used as an intermediary. Personally, I've never had good luck automating Acrobat. That's mostly because it's not super-easy and I quickly give up on it. You sound like you might have a legitimate reason to spend some time learning it. The first thing you should probably do is download the Software Developers Kit http://partners.adobe.com/public/dev...sdk/index.html It says you need a (free) ASN Web something or another. I don't know what that is, but the SDK claims to have VB sample code in it that would be extremely helpful, I'll bet. I don't believe that Adobe as VBA in any of it's products, so you'll have to do the automation from Excel. If you don't mind, let me know if you find SDK worthwhile. I might just check it out if it's at all helpful. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Excel count of 1 word if found in multi-word cells of column | Excel Worksheet Functions | |||
Excel 7, paste linked to word becomes black when word pdf'd | Excel Discussion (Misc queries) | |||
Copy from Word to Excel, and retain indent, plus word wrap | Excel Discussion (Misc queries) | |||
Print labels by using Excel data in a Word mail into word | Excel Discussion (Misc queries) | |||
Printing Word Document using Excel Programming hangs Word | Excel Programming |