Dear all,
I would like to copy data from an open excel file into a table in a Word 
document.
First I have to select a type number from the Excel file and paste it via a 
button in the Word document into he table.
Then VBA has to reactivate the Excel file and copy a codenumber next to the 
selected cell and past it into the next column of the Word table.
I use this code to do this:

Dim oExc As Excel.Application
Dim rTmp As Excel.Range
Set oExc = GetObject(, "Excel.application")
Set oWrk = oExc.ActiveWorkbook
Set oSht = oWrk.ActiveSheet
Set rTmp = oExc.ActiveCell
    Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
        wdInLine, DisplayAsIcon:=False
    Selection.TypeParagraph
    Selection.MoveRight Unit:=wdCell
    Selection.MoveRight Unit:=wdItem
    Selection.MoveLeft Unit:=wdCharacter, Count:=1

    Selection.Font.Size = 9
    Selection.Font.Bold = True
    Selection.Range.Text = rTmp.Offset(0, 1).Value
    Selection.EndKey Unit:=wdLine
    Selection.TypeParagraph
    
End Sub

This works fine when only one excel file is open.
When more Excel files are open the wrong one is selected.
How can I point to a certain Excel file?

Thanks in advance.

Hi,

You can get around this problem by having the macro ask for the file name 
and also prevent opening more than one instance of Excel using code like the 
following.

    Const Error_FileNotFound = 1004
    Const Error_NotRunning = 429
    Const Error_NotInCollection = 9
    Dim fileName As String
    Dim wkbName As String
    Dim xlApp As Object
    Dim wkBook As Object
    Dim newInstance As Boolean

    With Application.FileDialog(msoFileDialogFilePicker)
        If .Show Then
            fileName = .SelectedItems(1)
        Else
            MsgBox "You didn't select an Excel file to open."
            Exit Sub
        End If
    End With
    k = InStrRev(fileName, "\", -1, vbTextCompare)
    If k > 0 Then
        wkbName = Right(fileName, k - 1)
    Else
        MsgBox "A suitable file was not selected."
        Exit Sub
    End If
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number = Error_NotRunning Then
        Set xlApp = CreateObject("Excel.Application")
        MsgBox "A new instance of Excel was created."
        newInstance = True
    Else
        MsgBox "An open instance of Excel is being used."
        newInstance = False
    End If

    Set wkBook = xlApp.Workbooks(wkbName)
    If Err.Number = Error_NotInCollection Then
         Err.Clear
         Set wkBook = xlApp.Workbooks.Open(fileName)
         If Err.Number = Error_FileNotFound Then
              MsgBox "The file specified could not be opened.", _
                  vbCritical Or vbOKOnly, "File Not Opened"
              Set xlApp = Nothing
              Exit Sub
          End If
    End If

-- 
Hope this helps,
Pesach Shelnitz 


"DeCock" wrote:

> Dear all,
> I would like to copy data from an open excel file into a table in a Word 
> document.
> First I have to select a type number from the Excel file and paste it via a 
> button in the Word document into he table.
> Then VBA has to reactivate the Excel file and copy a codenumber next to the 
> selected cell and past it into the next column of the Word table.
> I use this code to do this:
> 
> Dim oExc As Excel.Application
> Dim rTmp As Excel.Range
> Set oExc = GetObject(, "Excel.application")
> Set oWrk = oExc.ActiveWorkbook
> Set oSht = oWrk.ActiveSheet
> Set rTmp = oExc.ActiveCell
>     Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
>         wdInLine, DisplayAsIcon:=False
>     Selection.TypeParagraph
>     Selection.MoveRight Unit:=wdCell
>     Selection.MoveRight Unit:=wdItem
>     Selection.MoveLeft Unit:=wdCharacter, Count:=1
> 
>     Selection.Font.Size = 9
>     Selection.Font.Bold = True
>     Selection.Range.Text = rTmp.Offset(0, 1).Value
>     Selection.EndKey Unit:=wdLine
>     Selection.TypeParagraph
>     
> End Sub
> 
> This works fine when only one excel file is open.
> When more Excel files are open the wrong one is selected.
> How can I point to a certain Excel file?
> 
> Thanks in advance.

Thank you for the answer.
Is it not possible to give a hardcoded name from the excel file that has to 
be activated?
The problem is that it is very well possible that more instances of excel 
are allready open when the word application is started.
Best regards

"Pesach Shelnitz" wrote:

> Hi,
> 
> You can get around this problem by having the macro ask for the file name 
> and also prevent opening more than one instance of Excel using code like the 
> following.
> 
>     Const Error_FileNotFound = 1004
>     Const Error_NotRunning = 429
>     Const Error_NotInCollection = 9
>     Dim fileName As String
>     Dim wkbName As String
>     Dim xlApp As Object
>     Dim wkBook As Object
>     Dim newInstance As Boolean
> 
>     With Application.FileDialog(msoFileDialogFilePicker)
>         If .Show Then
>             fileName = .SelectedItems(1)
>         Else
>             MsgBox "You didn't select an Excel file to open."
>             Exit Sub
>         End If
>     End With
>     k = InStrRev(fileName, "\", -1, vbTextCompare)
>     If k > 0 Then
>         wkbName = Right(fileName, k - 1)
>     Else
>         MsgBox "A suitable file was not selected."
>         Exit Sub
>     End If
>     
>     On Error Resume Next
>     Set xlApp = GetObject(, "Excel.Application")
>     If Err.Number = Error_NotRunning Then
>         Set xlApp = CreateObject("Excel.Application")
>         MsgBox "A new instance of Excel was created."
>         newInstance = True
>     Else
>         MsgBox "An open instance of Excel is being used."
>         newInstance = False
>     End If
> 
>     Set wkBook = xlApp.Workbooks(wkbName)
>     If Err.Number = Error_NotInCollection Then
>          Err.Clear
>          Set wkBook = xlApp.Workbooks.Open(fileName)
>          If Err.Number = Error_FileNotFound Then
>               MsgBox "The file specified could not be opened.", _
>                   vbCritical Or vbOKOnly, "File Not Opened"
>               Set xlApp = Nothing
>               Exit Sub
>           End If
>     End If
> 
> -- 
> Hope this helps,
> Pesach Shelnitz 
> 
> 
> "DeCock" wrote:
> 
> > Dear all,
> > I would like to copy data from an open excel file into a table in a Word 
> > document.
> > First I have to select a type number from the Excel file and paste it via a 
> > button in the Word document into he table.
> > Then VBA has to reactivate the Excel file and copy a codenumber next to the 
> > selected cell and past it into the next column of the Word table.
> > I use this code to do this:
> > 
> > Dim oExc As Excel.Application
> > Dim rTmp As Excel.Range
> > Set oExc = GetObject(, "Excel.application")
> > Set oWrk = oExc.ActiveWorkbook
> > Set oSht = oWrk.ActiveSheet
> > Set rTmp = oExc.ActiveCell
> >     Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
> >         wdInLine, DisplayAsIcon:=False
> >     Selection.TypeParagraph
> >     Selection.MoveRight Unit:=wdCell
> >     Selection.MoveRight Unit:=wdItem
> >     Selection.MoveLeft Unit:=wdCharacter, Count:=1
> > 
> >     Selection.Font.Size = 9
> >     Selection.Font.Bold = True
> >     Selection.Range.Text = rTmp.Offset(0, 1).Value
> >     Selection.EndKey Unit:=wdLine
> >     Selection.TypeParagraph
> >     
> > End Sub
> > 
> > This works fine when only one excel file is open.
> > When more Excel files are open the wrong one is selected.
> > How can I point to a certain Excel file?
> > 
> > Thanks in advance.

Hi,

Of course you can use a hard-coded name for the Excel file, but your code 
should still test whether the file is opened in the instance of Excel used. 
If the file is not opened, it should test that the file exists when it tries 
to open it. Your code should then be something like the following.

Const Error_FileNotFound = 1004 
Const Error_NotRunning = 429 
Const Error_NotInCollection = 9 
Dim fileName As String 
Dim wkbName As String 
Dim xlApp As Object 
Dim wkBook As Object 
Dim newInstance As Boolean 

fileName = "c:\MyPath\MyExcelFile.xls"
k = InStrRev(fileName, "\", -1, vbTextCompare) 
If k > 0 Then 
wkbName = Right(fileName, k - 1) 
End If 

On Error Resume Next 
Set xlApp = GetObject(, "Excel.Application") 
If Err.Number = Error_NotRunning Then 
Set xlApp = CreateObject("Excel.Application") 
MsgBox "A new instance of Excel was created." 
newInstance = True 
Else 
MsgBox "An open instance of Excel is being used." 
newInstance = False 
End If 

Set wkBook = xlApp.Workbooks(wkbName) 
If Err.Number = Error_NotInCollection Then 
Err.Clear 
Set wkBook = xlApp.Workbooks.Open(fileName) 
If Err.Number = Error_FileNotFound Then 
MsgBox "The file specified could not be opened.", _ 
vbCritical Or vbOKOnly, "File Not Opened" 
Set xlApp = Nothing 
Exit Sub 
End If 
End If 

-- 
Hope this helps,
Pesach Shelnitz 


"DeCock" wrote:

> Thank you for the answer.
> Is it not possible to give a hardcoded name from the excel file that has to 
> be activated?
> The problem is that it is very well possible that more instances of excel 
> are allready open when the word application is started.
> Best regards
> 
> "Pesach Shelnitz" wrote:
> 
> > Hi,
> > 
> > You can get around this problem by having the macro ask for the file name 
> > and also prevent opening more than one instance of Excel using code like the 
> > following.
> > 
> >     Const Error_FileNotFound = 1004
> >     Const Error_NotRunning = 429
> >     Const Error_NotInCollection = 9
> >     Dim fileName As String
> >     Dim wkbName As String
> >     Dim xlApp As Object
> >     Dim wkBook As Object
> >     Dim newInstance As Boolean
> > 
> >     With Application.FileDialog(msoFileDialogFilePicker)
> >         If .Show Then
> >             fileName = .SelectedItems(1)
> >         Else
> >             MsgBox "You didn't select an Excel file to open."
> >             Exit Sub
> >         End If
> >     End With
> >     k = InStrRev(fileName, "\", -1, vbTextCompare)
> >     If k > 0 Then
> >         wkbName = Right(fileName, k - 1)
> >     Else
> >         MsgBox "A suitable file was not selected."
> >         Exit Sub
> >     End If
> >     
> >     On Error Resume Next
> >     Set xlApp = GetObject(, "Excel.Application")
> >     If Err.Number = Error_NotRunning Then
> >         Set xlApp = CreateObject("Excel.Application")
> >         MsgBox "A new instance of Excel was created."
> >         newInstance = True
> >     Else
> >         MsgBox "An open instance of Excel is being used."
> >         newInstance = False
> >     End If
> > 
> >     Set wkBook = xlApp.Workbooks(wkbName)
> >     If Err.Number = Error_NotInCollection Then
> >          Err.Clear
> >          Set wkBook = xlApp.Workbooks.Open(fileName)
> >          If Err.Number = Error_FileNotFound Then
> >               MsgBox "The file specified could not be opened.", _
> >                   vbCritical Or vbOKOnly, "File Not Opened"
> >               Set xlApp = Nothing
> >               Exit Sub
> >           End If
> >     End If
> > 
> > -- 
> > Hope this helps,
> > Pesach Shelnitz 
> > 
> > 
> > "DeCock" wrote:
> > 
> > > Dear all,
> > > I would like to copy data from an open excel file into a table in a Word 
> > > document.
> > > First I have to select a type number from the Excel file and paste it via a 
> > > button in the Word document into he table.
> > > Then VBA has to reactivate the Excel file and copy a codenumber next to the 
> > > selected cell and past it into the next column of the Word table.
> > > I use this code to do this:
> > > 
> > > Dim oExc As Excel.Application
> > > Dim rTmp As Excel.Range
> > > Set oExc = GetObject(, "Excel.application")
> > > Set oWrk = oExc.ActiveWorkbook
> > > Set oSht = oWrk.ActiveSheet
> > > Set rTmp = oExc.ActiveCell
> > >     Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF, Placement:= _
> > >         wdInLine, DisplayAsIcon:=False
> > >     Selection.TypeParagraph
> > >     Selection.MoveRight Unit:=wdCell
> > >     Selection.MoveRight Unit:=wdItem
> > >     Selection.MoveLeft Unit:=wdCharacter, Count:=1
> > > 
> > >     Selection.Font.Size = 9
> > >     Selection.Font.Bold = True
> > >     Selection.Range.Text = rTmp.Offset(0, 1).Value
> > >     Selection.EndKey Unit:=wdLine
> > >     Selection.TypeParagraph
> > >     
> > > End Sub
> > > 
> > > This works fine when only one excel file is open.
> > > When more Excel files are open the wrong one is selected.
> > > How can I point to a certain Excel file?
> > > 
> > > Thanks in advance.