VBA Write to file error

[Terry Detrie]

I am working on a template for monthly reports; the top part of the
form is a table full of the usual parameters (Project Number, Leader,
Sponsor, Month, Hours Worked, etc). Management wants to be able to
grab all the values and dump the data into an Excel file.

My approach was to create bookmarks throughout the table. Say for
instance I had two cells. First one is Project Leader, and the next
one is Terry Detrie. I'd set bookmark for the first cell, then tab
over to the next and grab the selected text. I'd cycle through all
the bookmarks this way, save the text to an array, and then write the
array to a text file. This text file could then be read by Excel
(also via macro). If anyone thinks my approach is useful, I will post
the code.


Is there a more direct way to transfer data from Word to Excel?

If 'yes', which is easier, running the code from Excel (opening and
reading Word document) or from Word (opening and writing Excel
spreadsheet)?

Terry

 

[Jezebel]

There are several other methods, depending on how automated you want to be.

1. On Tools > Options > Save, check 'Save data only for forms'. Save the
document and you end up with a CSV file which Excel can read directly.

2. If you're going to the trouble of collecting the data into an array, you
can insert the array directly into a worksheet. It's easiest to use a
variant array, so you can handle both text and numbers, and empty values
come across as empty cells (rather than zeroes); and your array has to have
two dimension even if the second dimension is only 1 --

Dim pData(1 to 10, 1 to 1) as variant
Dim pxlSheet as Excel.Worksheet

:

pxlSheet.Range("A1:A10") = pData