Also, I wasn't talking about which Excel file to open, but what worksheet
WITHIN the Excel file to use. I'm coming to the following as a possible
deisgn for my app:
1. From my Excel file, store the outputs (header, data pairs) on one
worksheet called Data Table1, where Col A are the field names and Col B is
the value of the field (I have already made that modification).
2. Write a program to write the above data into a CSV file, the name of
which is determined by the Excel app. (This is essentially done as well). I
may embellish this with some of your code to allow the user to determine
where they want to save the file.
3. Write a program to open Word with a particular mail merge report
template, send it the path\name of the CSV and do a Merge to New Document.
Save the document with the same name as the CSV, except as a .doc. I am only
now beginning to put together how this might be done. The actual merge may
have to be done from the Word side, I don't know. This is wher I need the
most help.
Not sure this will solve the Mac problem, but it should make it easier to
generate reports in Windows.
--
Thanks,
Andy
"Doug Robbins - Word MVP" wrote:
You haven't studied the code, huh?
It presents the user with a dialog that allows them to browse to and select
the Excel file.
It could all be done from Excel - See the article "Control Word from Excel"
at:
http://www.word.mvps.org/FAQs/InterD...WordFromXL.htm
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
"AndyC812" wrote in message
...
That's outstanding, thanks for the effort. So, I would need a different
version of my report for 2007, 2003 and Mac? I have users who are in each
environment.
This macro would be run from the Word side? Is that correct? How does
the
macro know what worksheet to look for the data in?
Thanks again, Doug, I really appreciate it. I will also check on the
Excel
boards. I really would rather this be totally driven from the Excel side
of
the interface.
"Doug Robbins - Word MVP" wrote:
The following code, written for use with Word 2007 and an Excel 2007 data
source requires:
1. In the Visual Basic Editor - a reference must be set to the
Microsoft
Office 12.0 Access database engine Object Library
If using earlier versions of Office, the reference should be set
to
the Microsoft DAO 3.6 Object Library and the following line of the code
Set db = OpenDatabase(DataSource, False, False, "Excel 12.0;
IMEX=1;")
must be replaced with
Set db = OpenDatabase(DataSource, False, False, "Excel 8.0;
IMEX=1;")
I do not know the equivalent of these setup steps with a Mac. If
you
need assistance with that aspect of it, I would suggest that you post to
one
of the Microsoft.Public.Mac.Office newsgroups.
2. In the Excel spreadsheet that is selected when the code is run,
there
is a two column list with "field names" in the first column and the
corresponding values in the second column. It assumes that the first row
is
a header row - probably containing the column titles "field name" and
"value" (but what is actually in the cells of that row does not really
matter. The list, including the header row must be named "DataItems"
When the code runs, it will create a document variable for each record in
the datasource with the name of the variable being the item in the first
column of the spreadsheet and the value of the variable being the item in
the second column of the spreadsheet.
To facilitate the insertion of the Docvariable fields in the document, I
would create a userform with a listbox that was populated with the field
names from the first column of the spreadsheet and a button that when
clicked would insert a docvariable field into the documnet for the field
that was selected in the listbox. It would in fact be possible to
construct
a user interface for this that had virtually the same functionality as
the
mail merge interface.
Dim Msg, Style, Title, Response
Dim i As Long
Dim j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varname As String
Dim varvalue As String
Dim fd As FileDialog
Dim DataSource As String
Dim avar As Variable
For Each avar In ActiveDocument.Variables
avar.Value = " "
Next avar
'Display the file selection dialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
.Title = "Select Data Source"
.InitialFileName = ""
If .Show = -1 Then
DataSource = .SelectedItems(1)
Else
Exit Sub
End If
End With
Set fd = Nothing
Msg = "Please ensure that Excel is closed before Continuing. Do you want
to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Refresh Data" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
' Open the file containing the Data
Set db = OpenDatabase(DataSource, False, False, "Excel 12.0;
IMEX=1;")
Set rs = db.OpenRecordset("SELECT * FROM `DataItems`")
With rs
.MoveFirst
For i = 0 To .RecordCount
ActiveDocument.Variables(Format(.Fields(0).Value)) =
..Fields(1).Value
.MoveNext
Next i
End With
' Cleanup
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Else
Exit Sub
End If
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
"AndyC812" wrote in message
...
OK, so you think this will also fix my problem with Mac reports also?
So
let
me see, I need to transpose my data tab, headers in A and data in B.
Then
I
need to replace all of the merge fields in my report documents with
docvariable fields. Then I need to write a VBA procedure that will
read
the
header, data pairs and send them to the correct Word template. Is that
all?
:-) Would it be asking too much for some sample code to get me
started?
I
really appreciate you sticking with both of my questions!
-Andy
"Doug Robbins - Word MVP" wrote:
Yes. In fact it must be done with VBA.
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
"AndyC812" wrote in message
...
Docvariable fields! That sounds promising. I'll read up on those.
Could
I
use VBA to also drive the report generation in Word? I would like
to
be
able
to push a button in the Excel file and have a "merged" report pop up
in
Word
based on the field data in the spreadsheet.
"Doug Robbins - Word MVP" wrote:
In that case, you could have the "field names" in one column and
the
data
for each "field" in the cells of the adjacted column and then you
would
not
be limited by the number of columns available.
Of course, you would then have to use something other than
mailmerge
to
create the report and the way that I would do it would be to use
Docvariable
fields in the document in place of merge fields and use VBA to
iterate
through the rows of the spreadsheet, creating variables in the
document
with
the name of the variables being the "field names" from the first
column
and
the values of the variables being set to the data from the
corresponding
cells in the second column.
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of
my
services on a paid consulting basis.
Doug Robbins - Word MVP
"AndyC812" wrote in message
...
Just one report. Each spreadsheet generates one LONG reord of
data
based
on
user input to the other worksheets.
"Doug Robbins - Word MVP" wrote:
Are you preparing multiple reports from the data or is it just
one
report?
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself
of
my
services on a paid consulting basis.
Doug Robbins - Word MVP
"AndyC812" wrote in message
...
My application has multiple tabs and generates a 7-9 page
report
in
Word
using 250 merge fields. Not all the fields are used in all
the
reports,
but
I'm running out of room. Others have suggested CSV also, but
can
I
automate
it as well (generate the CSV from Excel and "Send it" to the
proper
merge
template and do a "Merge to New Document" and perhaps even
rename
the
resulting file.
Thanks!
"Doug Robbins - Word MVP" wrote:
I find it hard to imagine a mail merge that needs that many
fields.
However, if you do need to exceed the 255 column limit of
Excel,
you
can
use
a .csv file as the data source.
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail
yourself
of
my
services on a paid consulting basis.