Excel Workbook Name & Worksheet Name

[Keith]

I am trying to find a way to capture an Excel Workbook Name (file
name) and the name of its 1st Worksheet.

I have attempted two different methods for the last two days, and I
have been unsuccessful.
Attempt 1: I have tried to captured this information in Excel (a
message box spits out this information that is stored in a variable).
I am unable to figure out how to get this information into my Word
document.
Attempt 2: I have tried to capture this information directly from
Word. In my many attempts, I am not able to set this up properly to
get past all of the declarations.

I have set both Excel and Word to read the object library of the
other. I feel that this should be a couple simple commands especially
as I already have this information in an Excel varible (that I tried
to declare as Public and pass to Word).

What I am trying to do is have a macro do everything to run a
mailmerge with no intervention from the user. The user opens a
Personal.xls spreadsheet (or already has it saved to their computer).
They run a macro that creates a new worksheet (sheet 1) sorts the
data, creates a new column with modified data, then calls a Word
document to run its macro. (Note: I am trying to make it so ONLY
this Word document (merge template) has a specific name and location.
I want to be able to use any active Excel document to call it). Word
pulls up a userform to input some information, runs a merge (only with
a specific file and worksheet name at the moment), and saves the newly
merged document. Every last item should be relative except for the
location of the Word document that serves as the merge template. I
can make the Excel document relative if Word can receive the variable
with this information, or read the name of the active workbook and its
first worksheet.

I would be MOST greatful for help, as I am not sure which small step I
am messing up.

Keith

 

[Helmut Weber]

Hi Keith,

see:

http://word.mvps.org/faqs/interdev/controlxlfromword.htm
http://word.mvps.org/faqs/interdev/controlwordfromxl.htm
http://word.mvps.org/faqs/interdev/EarlyvsLateBinding.htm

Example for passing data from Excel to Word:

In Excel:

Sub SendToWord()
Dim oWrd As Word.Application
Set oWrd = GetObject(, "Word.Application")
With oWrd
.Activate
.Run "GetfromExcel"
End With
End Sub

In Word:

Sub GetFromExcel()
Dim oExc As Excel.Application
Set oExc = GetObject(, "Excel.application")
With oExc.ActiveWorkbook
MsgBox .Name & Chr(13) & .Sheets(1).Name
End With
End Sub


I don't know much about mailmerge.


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Keith]

Helmut,
I want to thank you so very much. I am now able to get the File name
and the worksheet name. I had tried so many different methods, that I
am not entirely sure what it was that I was not doing. My Excel code
was fine so I just updated my Word code. Here is my final result.


Dim appXL As Excel.Application
Dim strExcelFileName As String, strExcelWkshtName As String

Set appXL = GetObject(, "excel.application")
With appXL.ActiveWorkbook
strExcelFileName = .FullName
strExcelWkshtName = .ActiveSheet.Name
End With

When I use strExcelFileName in two different places in my mail merge,
it works fine. I have to declare the sheet in one other place in the
mail merge, and I have not got that to work, but that is a different
issue. I expect it is expecting text and not a variable, and I have
to see if it can take a variable.

Thank you so much for your help!