Send data from Word VBA Project to New Excel Worksheet

[gmaxey]

Hi,

I have written some code that collects files from a path\folder using FileSytemObject and then loops through each file and writes data about the file (name, last saved, etc.) into a Word document as text or into a Word table as row\cell data.

I would like to expand on this and have the option to write the file data to new Excel worksheet. Dumbing it done to a level I understand:,

Create Instance of Excel
Create New WorkSheet
Create column headings e.g., Name, Path, SavedDate, Type, Size, etc.
For Each oFile in Files
Worksheet.Rows.Add
Send data about file eg., Name, Path, SavedDate, Type, Size, etc. to
Next oFile

Appreciate any code examples or nudges in the right direction to get started. Would I do this using a ADO connection? If so need a little help there as well.

Thanks.

[Bob Phillips]

Thjis should get you started


Dim oFile

Dim xl As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xl = CreateObject("Excel.Application")
Set xlBook = xl.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
With xlSheet

For Each oFile In Files

i = i + 1
.Cells(i, "A").Value = oFile.Name
.Cells(i, "B").Value = oFile.Path
.Cells(i, "C").Value = oFile.SavedDate
'etc.
Next oFile
End With

xlBook.SaveAs Filename:="myFile.xlsx"
xlBook.Close SaveChanges:=False

Set xlSheet = Nothing
Set xlBook = Nothing
Set xl = Nothing

[gmaxey]

XLD,

Once again thank you. I think I can make this work.

[gmaxey]

XLD,

Your pointers certainly got me going and I've actually managed to achieve what I was after. However, for the sake of knowledge, I was wondering if there might be any efficiency or other advantages to:


Creating the new worksheet as you've shown and then:
Establish a ADO connection to the worksheet
Pass the data via the ADO connection

If so, could you (or anyone else) provide another nudge?

Thanks.

[snb]

You can save the Word document as a csv file and open it in Excel using:


Sub M_snb()
getobject( "G:\OF\savedworddocument.csv").visible=true
End Sub

or use:


Sub M_snb()
ThisDocument.Tables(1).Range.Copy

With CreateObject("excel.application").workbooks.Add
.Application.Visible = True
.sheets(1).Paste .sheets(1).Cells(1)
End With
End Sub

[Bob Phillips]

Greg, is the data in a table, a named range, or just a sheet?

[gmaxey]

xld,

It is just a sheet. I tinkered some with the DAO connection idea to add the records, but since I am starting each processs with a new spreadsheet and if the users cancels in progress the new sheet is intentionally closed without saving then I think what I have will work nicely. Thank you!