[WORD VBA] Process data from an already-opened Excel file

[Paulo_3456]

Hi everyone,

I have a Word document with 2 macros in it.

I coded a first Word VBA Macro that imports tables from an Excel file.
This first Word Macro begins allowing User to select the Excel file thanks to a dialog box.
Then, the first macro execute itself and import some tables in the Word document.
After that, first macro ends and user has to annotate some lines of the imputed Excel tables in the Word document.

Then, user can execute the second macro which process datas according to the annotations he just made and I need this second macro to get some more tables from the already-opened Excel file.

But I can't figure out a way to do it without asking again the user to select the Excel file in the directory.

Isn't there a way to use the already-opened Excel file instead of reopening it ?

I am not sure to be perfectly clear, if you need any details, I can provide some more information.

Regards,
Paul

 

[Macropod]

Without seeing your code, it's impossible to give specific advice. However, it seems you should be able to declare the variables used for this as common ones (i.e. put the Dim statement ahead of the first sub), so the second sub can reference whatever you've populated it with.

 

[Paulo_3456]

My bad, here is my code (I simplified it for reading purpose).
I am forced to divide the macro in 2 macros because User has to modify the word File in between.
My concern is to use directly the already-opened Excel File in the 2nd macro rather than re-open the Excel file.
I have also an issue with closing the Excel file at the end...

Thanks a lot for helping !

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''' 1ST MACRO FOR THE USER  '''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Sub Import_tableaux_SRM()
Dim intChoice As Integer
Dim strPath As String
Dim year_begin As String
Dim year_end As String
Dim new_entity As String
Dim former_entity As String
Dim tbl As Table


Application.ScreenUpdating = False

'Open Dialog BOX
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Call AutomateExcel(strPath)
End If


End Sub



'Macro Import Tables from Excel File


Private Sub AutomateExcel(ByVal strPath As String)
Dim objExcel As Object
Dim objWorkbook As Object


'Open Excel file
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.workbooks.Open(strPath)


'Copy-paste table [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  from Excel to Word
objWorkbook.sheets("Bilan").Cells(4, 2).Resize(23, 7).Copy
Selection.GoTo What:=wdGoToBookmark, Name:="Bilan"
Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=True




'Copy-paste table [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]  from Excel to Word
objWorkbook.sheets("P&L").Cells(4, 2).Resize(43, 7).Copy
Selection.GoTo What:=wdGoToBookmark, Name:="CdR"
Selection.PasteExcelTable _
        LinkedToExcel:=False, _
        WordFormatting:=False, _
        RTF:=True


End Sub





''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''' 2ND MACRO ONCE THE USER TYPED SOME ANNOTATIONS IN THE IMPORTED TABLES  '''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Sub RA_auto()


Dim objExcel As Object
Dim objWorkbook As Object
Dim i As Integer
Dim intChoice As Integer
Dim year_begin As String
Dim year_end As String
Dim strPath As String


'DIALOG BOX
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)


'RE-Open Excel File
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.workbooks.Open(strPath)


Selection.TypeParagraph

With ActiveDocument.Tables(1)

.Select
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.TypeParagraph


'LOOP to read the characters the user typed in order to import some more Excel tables

For i = 2 To .Rows.Count

If (Left(.Cell(i, 1).Range.Text, Len(.Cell(i, 1).Range.Text) - 2)) = "some text" Then

objWorkbook.sheets("Bilan").Cells(4, 2).Resize(13, 5).CopyPicture Appearance:=xlScreen, Format:=xlPicture
Selection.Paste

Selection.TypeParagraph


End If


Next i


End With


'Close Excel
Application.DisplayAlerts = False
objExcel.Quit
Set objExcel = Nothing


End Sub

 

[Paulo_3456]

@Macropod

I tried to do what you said and type these lines ahead of my module instead of declaring it for each macro :

Dim strPath As String
Dim objExcel As Object
Dim objWorkbook As Object

But then I get an error at this line :
objWorkbook.sheets("Bilan").Cells(4, 2).Resize(23, 7).Copy

'Object or variable not defined'

Can I declare the variable strPath at the beginning ?

 

[Macropod]

Try:

Option Explicit
Dim objExcel As Object, objWorkbook As Object, strPath As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''' 1ST MACRO FOR THE USER  '''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Import_tableaux_SRM()
Dim intChoice As Long
Application.ScreenUpdating = False

'Open Dialog BOX
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
  strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
  Call AutomateExcel
End If
Application.ScreenUpdating = True
End Sub

'Macro Import Tables from Excel File
Private Sub AutomateExcel()

'Open Excel file
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.workbooks.Open(strPath)

'Copy-paste table [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]   from Excel to Word
objWorkbook.sheets("Bilan").Cells(4, 2).Resize(23, 7).Copy
Selection.GoTo What:=wdGoToBookmark, Name:="Bilan"
Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True

'Copy-paste table [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL]   from Excel to Word
objWorkbook.sheets("P&L").Cells(4, 2).Resize(43, 7).Copy
Selection.GoTo What:=wdGoToBookmark, Name:="CdR"
Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' 2ND MACRO ONCE THE USER TYPED SOME ANNOTATIONS IN THE IMPORTED TABLES  ''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RA_auto()
Dim i As Long, intChoice As Long

If objExcel Is Nothing Then
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
End If

'RE-Open Excel File
If objWorkbook Is Nothing Then Set objWorkbook = objExcel.workbooks.Open(strPath)

Selection.TypeParagraph
With ActiveDocument.Tables(1)
  .Select
  Selection.MoveDown Unit:=wdLine, Count:=1
  Selection.TypeParagraph

  'LOOP to read the characters the user typed in order to import some more Excel tables
  For i = 2 To .Rows.Count
    If (Left(.Cell(i, 1).Range.Text, Len(.Cell(i, 1).Range.Text) - 2)) = "some text" Then
      objWorkbook.sheets("Bilan").Cells(4, 2).Resize(13, 5).CopyPicture Appearance:=xlScreen, Format:=xlPicture
      Selection.Paste
      Selection.TypeParagraph
    End If
  Next i
End With

'Close Excel
objWorkbook.Close False
objExcel.Quit
Set objWorkbook = Nothing: Set objExcel = Nothing
End Sub

 

[Paulo_3456]

Thanks for replying, I get the error 91 'OBject Variable With block or variable not set' on this line when it tries to copy the first table in the second macro.

objWorkbook.sheets("Bilan").Cells(4, 2).Resize(13, 5).CopyPicture Appearance:=xlScreen, Format:=xlPicture

 

[Macropod]

Try changing:
If objWorkbook Is Nothing Then Set objWorkbook = objExcel.workbooks.Open(strPath)
to:

If objWorkbook Is Nothing Then
  Set objWorkbook = objExcel.Workbooks.Open(strPath)
Else
  Set objWorkbook = objExcel.Workbooks(strPath)
End If

 

[Paulo_3456]

Well done ! It is working just fine Hope to reach your level someday !