Word to Excel

[Hari]

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

 

[Dick Kusleika]

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.

 

[Hari]

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

 

[Dick Kusleika]

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/developer/acrobat/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.

 

[Hari]

Hi Dick,

Im sorry, I couldnt respond a little earlier. I went to the acrobat
link u gave. In that "Acrobat7GMHdrSDKWin.zip" the file was available
as a free download (rest required subscription..). I downloaded it and
within it a samples folder had Microfot visual studio file and a Visual
C++ file but not for VB. I must be missing something.
Since i couldnt find VB code( I dont know VC etc and presently not much
time to learn it) I dropped the idea of exploring acrobat etc.

Thnx for your help. (I owe the "select" method to you)

Regards,
Hari