Thread: Word to Excel
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Dick Kusleika[_4_] Dick Kusleika[_4_] is offline
external usenet poster
 
Posts: 595
Default 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