Read excel from word macro works only once

[goulabf9]

Hi,

I use the following function to read the first two columns of an excel document. It works well but only once: that means if I close the word document and re-open then next time I launch the function, I get a 1004 error stating that the excel file name is not valid.
I found one solution to this problem: copy all the used files (word, excel) in a new directory and then it works again ... but only once.
So well, it seems like something is not closed properly but I don'see what. Hope someone can help me !

Thanks,

Fab


Function Read_Excel_File(ByVal MyFile As String) As Variant
Dim XlApplication As Excel.Application
Dim XlClasseur As Excel.Workbook
Dim XlFeuille As Excel.Worksheet
Dim XlCellule As String
Dim Search_Strings(1000, 2) As Variant
Dim i, NbrSearch As Integer
Dim XlToSearch, XlToReplace As String

Set XlApplication = CreateObject("Excel.Application")
Set XlClasseur = XlApplication.Workbooks.Open(MyFile)
Set XlFeuille = XlClasseur.Worksheets(1)

XlApplication.Visible = True
XlFeuille.Visible = xlSheetVisible


NbrSearch = XlFeuille.Cells(1, 5).Value
For i = 1 To NbrSearch
Search_Strings(i, 1) = XlFeuille.Cells(i, 1).Value
Search_Strings(i, 2) = XlFeuille.Cells(i, 2).Value
Next

XlClasseur.Close

Set XlFeuille = Nothing
Set XlClasseur = Nothing
XlApplication.Quit
Set XlApplication = Nothing

Read_Excel_File = Search_Strings

End Function

[Tinbendr]

Welcome to VBAExpress.

If you're posted this elsewhere, please provide the link, or at the very least, the website and title used.

When it errors, does MyFile, in fact, contain the proper filename?

I would move the XLApplication.Quit to just under the XlClasseur.Close.

Couple of other things. (Not that these are related to your problem.)

Dim i, NbrSearch As Integer
Dim XlToSearch, XlToReplace As String Should be changed to

Dim i as Long, NbrSearch As Long
Dim XlToSearch as String, XlToReplace As String In references about declaring variables, it states that you can do this, but the undeclared variable is declare as a variant, not the ending declaration. Also, VBA now converts all Integers to Long anyway, so save a step here.

David

[goulabf9]

Hi,

thanks for the reply. I will try later on at work.
Yes that's right, MyFile contains the proper name and actually the function is right since it works perfectly until the word doc is closed.

I also checked that there is no excel.exe instance in the task manager after the execution of the function but somehow something is not closed because I also had a crash yesterday on another excel file and then Excel tried to recover not only the file which crashed but also th excel file read in this function (myfile), although it was not open at the crash time.

Rgds

Fab

[goulabf9]

Hi,

thanks for the reply. I will try later on at work.
Yes that's right, MyFile contains the proper name and actually the function is right since it works perfectly until the word doc is closed.

I also checked that there is no excel.exe instance in the task manager after the execution of the function but somehow something is not closed because I also had a crash yesterday on another excel file and then Excel tried to recover not only the file which crashed but also th excel file read in this function (myfile), although it was not open at the crash time.

Rgds

Fab

Hi,

i tried again today with the modifications mentioned here above. Unfortunately, always the same: works once but at the next opening of the word doc, I get an error 1004.

Regards

Fab

[Tinbendr]

Try replacing

Set XLApplication = CreateObject("Excel.Application")
with
On Error Resume Next
Set XLApplication = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
Set XLApplication = CreateObject("Excel.Application")
End If

On Error GoTo 0

[goulabf9]

Hi,

thanks a lot !!
This works now, I can close and re-open word how many times I want and the function works.
I still have sometimes another error 1004 but for another reason which I know but can not solve:

I have two text fields in a userform where I store the name of the excel file I want to open. So I set the caption to the result of a filedialog. It works well, no problem.
To avoid having to enter the file every time I run the macro, I copy the file in the word document, then I give it back to the caption. Well don't know if it is clear ... let's try with VBA code:


Dim Search_File, MyDir As String
Dim Batch_Files As Variant
Option Explicit

Private Sub UserForm_Initialize()
ActiveDocument.Paragraphs(4).Range.Select
Excel_File.Caption = Selection.Text
Search_File = Selection.Text
End Sub


So when I open the userform, I read the paragraph 4 and set the caption accordingly

then later on:


Private Sub Search_Replace_Click()
Dim Search_Strings As Variant

Search_Strings = Read_Excel_File(Search_File)

End Sub


then I got an error 1004 that it can not find the file.

If inbetween I have the additional step


Private Sub Change_Excel_File_Click()
Dim FileToOpen As Variant
FileToOpen = ShowFileDialog(msoFileDialogFilePicker, "xls")
Excel_File.Caption = FileToOpen(0)
Search_File = FileToOpen(0)
ActiveDocument.Paragraphs(4).Range.Select
Selection.Text = "" & Chr(13)
Selection.InsertBefore (Search_File)
End Sub


then it works.

Any idea ?

Regards,

Fab

[fumei]

I am having a hard time following the value of Search_File.

At one point you have:

Search_File = FileToOpen(0)

the first item in an array.

On the other hand you have:

Search_File = Selection.Text

where you have selected text in a document.

This is not a good idea. Which is it?

[goulabf9]

I am having a hard time following the value of Search_File.

At one point you have:

Search_File = FileToOpen(0)

the first item in an array.

On the other hand you have:

Search_File = Selection.Text

where you have selected text in a document.

This is not a good idea. Which is it?

Hi,

right it is not a good idea ... let me explain what I wanted to do:
I select a file through a userform and copy the file name to a textbox but next time I start word, I have no way to know what was the last file which was selected: textbox is empty.
So I had the idea to copy the filename to a paragraph and then next time I start word, I back up the filename from the paragraph

So basically, I read the Search_File from the paragraph when the userform is loaded and I overwrite or not if the "select file" button is pressed.

In fact I just want Word to remember which file was selected by the last use of the document ...

don't know if it is clear ...

Fab

[fumei]

Where is this code?

You can use DOCVARIABLE to store a variable value after the code terminates. That is what it for.

So...where is the userform? In a document? In a template?

You do not state the name of the textbox, but assuming you saved th erpevious value the LAST time...
Sub UserForm_Initialize()
txtWhatever.Text = ActiveDocument.Variables("TheLastTime").Value
End Sub

The value of TheLastIme is put into the textbox txtWhatever.

Now to put it in the DOCVARIABLE, assuming you are still going to use Search_File as a string variable:

Sub WhatEverProcedureYouUseToCloseThe_UserForm()
ActiveDocument.Variables("TheLastTime").Value = Search_File


This could be added right up front:
Private Sub Change_Excel_File_Click()
Dim FileToOpen As Variant
FileToOpen = ShowFileDialog(msoFileDialogFilePicker, "xls")
Excel_File.Caption = FileToOpen(0)
Search_File = FileToOpen(0)
ActiveDocument.Variables("YaddaYadda").Result = Search_File
End Sub

[goulabf9]

Thanks, I'll try it, I didn't know about docvariable.

by the way, here is the code of the userform (it is in a doc):


Dim Search_File, MyDir As String
Dim Batch_Files As Variant
Option Explicit

Private Sub Change_Excel_File_Click()
Dim FileToOpen As Variant
FileToOpen = ShowFileDialog(msoFileDialogFilePicker, "xls")
Excel_File.Caption = FileToOpen(0)
Search_File = FileToOpen(0)
ActiveDocument.Paragraphs(4).Range.Select
Selection.Text = "" & Chr(13)
Selection.InsertBefore (Search_File)
End Sub

Private Sub Change_Output_Path_Click()
Dim DirectoryToOpen As Variant
DirectoryToOpen = ShowFileDialog(msoFileDialogFolderPicker, "")
Text_Output.Caption = DirectoryToOpen(0)
MyDir = DirectoryToOpen(0)
ActiveDocument.Paragraphs(2).Range.Select
Selection.Text = "" & Chr(13)
Selection.InsertBefore (MyDir)
End Sub

Private Sub Close_Form_Click()
Me.Hide
End Sub

Private Sub Open_Input_Click()
Dim FileToOpen As Variant
FileToOpen = ShowFileDialog(msoFileDialogFilePicker, "doc")
Text_Input.Caption = FileToOpen(0)
Batch_Files = FileToOpen
End Sub


Private Sub Print_PDF_Click()
Dim i As Integer
With Dialogs(wdDialogFilePrintSetup)
.Printer = "Adobe PDF"
.DoNotSetAsSysDefault = True
.Execute
End With
While Batch_Files(i) <> ""
Documents.Open (Batch_Files(i))
ActiveDocument.PrintOut OutputFilename:="test.pdf"
i = i + 1
ActiveDocument.Close
Wend
Windows("Search_Replace.doc").Activate
End Sub

Private Sub Search_Replace_Click()
Dim i As Integer
Dim MyFile As String
Dim CloseFile As Boolean
Dim Search_Strings As Variant

Search_Strings = Read_Excel_File(Search_File)

CloseFile = Not (Keep_Open.Value)
While Batch_Files(i) <> ""
MyFile = Batch_Files(i)
Call Search_Replace_Sub(Search_Strings, MyDir, MyFile, CloseFile)
i = i + 1
Wend
Windows("Search_Replace.doc").Activate
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Text_Input.Caption = ""
Text_Output.Caption = ""
Excel_File.Caption = ""
'ActiveDocument.Paragraphs(2).Range.Select
'Text_Output.Caption = Selection.Text
'MyDir = Selection.Text
'ActiveDocument.Paragraphs(4).Range.Select
'Excel_File.Caption = Selection.Text
'Search_File = Selection.Text
End Sub

[fumei]

There are a number of things I could comment on, but this one I shall:
Private Sub Close_Form_Click()
Me.Hide
End Sub
The name of the procedure is Close_Form, except this does NOT close the userform. It hides it. They are different. If you are terminating the userform, then close it. You use Unload Me.

[goulabf9]

There are a number of things I could comment on, but this one I shall:
Private Sub Close_Form_Click()
Me.Hide
End Sub
The name of the procedure is Close_Form, except this does NOT close the userform. It hides it. They are different. If you are terminating the userform, then close it. You use Unload Me.

Oh yes I know this one, i actually just changed from Unload to Hide as I had problems to see if was changing something.

If you have time, please give some comments on the code. I know it is not a nice code, since I am not a programmer, just an engineer who tries to automize some tasks and I am just happy when it works. Mostly I use Excel, it is my first macro with Word.
The goal of this project is actually to search in one or more word documents the words which are in the first column of an excel sheet and replace by the words which are in the second column.
Well a kind of "batch search&replace in word files via an excel configuration file". I did like it:
- open the excel file, read the first columns and save the words in an array
- batch open the doc files, search&replace, rename and close the files

I'll try your docvariable tip today. I let you know.

Regards,

Fab