Word 97 Macro to convert a table into Excel

[Mickey]

Hi there,
Here's the thing,
From an open word document which will have a table that may go across multiple pages, and will probably just be 2 coulumns wide, I want to be able to click a toolbar icon (which will be this macro) and then I want to specify a path & filename of an excel sheet (either existing or new),

[NOTE : preferably this could be done via windows gui rather than type]

Then I need the table to magically appear in the excel sheet . Therefore row 1 column 1 of the word table will need to go to cell A1 in the excel sheet. Likewise row 1 column 2 on the word table will need to go to B1 in the excel sheet.. etc, etc,
As such row 2 column 1 in the word table will need to go to A2 in the excel sheet.

I have done some VB.net programming but haven't done any VBA stuff or played around with macros B4...

Can anyone help please...

Thanks,
Mike

[Killian]

Hi Mike,

This appears to me to be a simple copy/paste scenario, the trick is to just to get your head around working with the MS Office object model so you can control different apps. If you've worked with VB.net then it shouldn't be too tricky for you to find your way around once you get going...
I've attached an commented example that does what you describe using a custom userform to select new or existing and a windows dialog to select the existing file.
It's in Word2003 - let me know if you need a different version

Enjoy
K :-)

[TonyJollans]

In Word 97 you will need to use API calls to get File Dialogs.

This action, though, just splatting the Word table into the top of a Worksheet, seems more likely to want a new file as a target than an existing one so I don't know if it's worth the effort.

[Killian]

Note to self: Read question before posting anwer

So, office 97 then...
I tried this out but came up with some permission issues running Off97 on XP on a virtual machine on my flakey network (can't imagine why...)

So here's some (untested in 97) code that should at least get you started. I know that the dialog boxes are available in 97 ('xlApp.Dialogs(xlDialogOpen).Show' should do something) but I've just stuck with creating a new workbook for the sake of clarity (=too lazy).

Enjoy
K :-)
[VBA]Sub ExportToExcel()
'!!! first add reference to Excel8 in Tools|References
Dim xlApp As Excel.Application 'dimension some (XL) objects to set as destination
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
'check if we're in a table
If Not Selection.Information(wdWithInTable) = True Then
MsgBox "Cursor must be within a table"
Else
'in the background, create an instance of XL
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
'select the current table and copy
Selection.Expand (wdTable)
Selection.Copy
'go to XL sheet, first cell and paste
xlSheet.Range("A1").Select
xlSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
'show XL
xlApp.Visible = True
'tidy up
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If
End Sub[/VBA]

[TonyJollans]

Y'know when I read the question I knew Word 97 didn't have the required Dialog but I never made the leap to the fact that Excel 97 did have it, and Excel was wanted. Good stuff, Killian!

[Mickey]

Hey guys, just wanted to say a big thanks for your efforts and replies.
Much appreciated.
I'll give it a go.
Cheers,
Mike