Excel 'Replace' action in VBA code fails on second pass (late binding)

In one of my MsAccess modules, the attached code will open a template file, select a row and do a replace.
Then the template is saved as a new file and the new file opens.

But if I run it a second time, with Excel still open, with a different new filename, it fails at line 100.

I'm thinking this is an easy fix. Just can't figure it out.
See attached code and sample template file.

After copying my test file to your c:\ folder and loading the code, simply run
fnExcelTest("C:\TextFile.xls")
and then run it again with a different destination filename:
fnExcelTest("C:\TextFilePass2.xls") ExcelTemplateTest.xls
Option Explicit

Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, _
  ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Function fnExcelTest(strFileNewName As String)

Dim objApp As Object, objAppBook As Object
Dim objXLApp As Object, objResultsSheet As Object
Dim xlPart As Integer, xlByRows As Integer

10    On Error GoTo fnExcelTest_ERR

'Set Up some Excel constants
20    xlPart = 2
30    xlByRows = 1

        '**Create an excel object, book object , and app object
40    Set objApp = CreateObject("Excel.Application")
50    Set objAppBook = GetObject("c:\ExcelTemplateTest.xls")
60    Set objResultsSheet = objAppBook.Worksheets(1)

      'For debugging, show the excel object and worksheets
70    objAppBook.Parent.Windows(objAppBook.Name).Visible = True
80    objAppBook.Parent.Visible = True

      'Select a row
90    objResultsSheet.Rows("5:5").Select
      'Do a simple replace
100   objApp.Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    
      'Save it with the new name then open it
110   objAppBook.SaveAs strFileNewName

      'Close objects
120   objApp.Workbooks.Close
130   objApp.Quit
140   Set objApp = Nothing

   
fnExcelTest_EXIT:
      Dim lngRetShell As Long
150   lngRetShell = ShellExecute(0, "OPEN", strFileNewName, "", "", 0)


160   Exit Function

fnExcelTest_ERR:

170   MsgBox "The error " & Err & " occurred at line " & Erl & vbNewLine _
      & "ERROR: " & Err.Description
180         Stop
190    Resume fnExcelTest_EXIT


End Function

Open in new window

askolitsAsked:
Who is Participating?
 
LucasMS Dynamics DeveloperCommented:
This has been kind of bugging me.  Try this piece of code.  I commented out line 50 and replaced it with something else.  
Function fnExcelTest(strFileNewName As String)

Dim objApp As Object, objAppBook As Object
Dim objXLApp As Object, objResultsSheet As Object
Dim xlPart As Integer, xlByRows As Integer

10    On Error GoTo fnExcelTest_ERR

'Set Up some Excel constants
20    xlPart = 2
30    xlByRows = 1

        '**Create an excel object, book object , and app object
40    Set objApp = CreateObject("Excel.Application")
'50    Set objAppBook = GetObject("c:\ExcelTemplateTest.xls")
60

       Set objAppBook = objApp.workbooks.Open("c:\ExcelTemplateTest.xls")
       Set objResultsSheet = objAppBook.Worksheets(1)
      'For debugging, show the excel object and worksheets
70    objAppBook.Parent.Windows(objAppBook.Name).Visible = True
80    objAppBook.Parent.Visible = True

      'Select a row
90    objResultsSheet.Rows("5:5").Select
      'Do a simple replace
100   objApp.Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    
      'Save it with the new name then open it
110   objAppBook.SaveAs strFileNewName

      'Close objects
120   objApp.workbooks.Close
130   objApp.Quit
140   Set objApp = Nothing

   
fnExcelTest_EXIT:
      Dim lngRetShell As Long
150   lngRetShell = ShellExecute(0, "OPEN", strFileNewName, "", "", 0)


160   Exit Function

fnExcelTest_ERR:

170   MsgBox "The error " & Err & " occurred at line " & Erl & vbNewLine _
      & "ERROR: " & Err.Description
180         Stop
190    Resume fnExcelTest_EXIT


End Function

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Try replacing:


110   objAppBook.SaveAs strFileNewName
with
110   objAppBook.SaveCopyAs strFileNewName

Chris
0
 
askolitsAuthor Commented:
hmmm.. Not sure why that would fix it. I did try it, but all it did was prompt me to save the template file.
Still breaks. Did you actually try it? Did it work for you?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LucasMS Dynamics DeveloperCommented:
Do this:

Create constants in your module where this code is

const xlPart = 2
const xlByRows = 1

Then try it.

The reason why it breaks is because it cannot resolve settings.  To find out the numerical value of them, simply open up excel press ALT+F11, then CTRL+G to bring up the debugger and type in ?xlByRows and it will give you the numerical equivalent.
0
 
askolitsAuthor Commented:
Didn't I already do that in the code?

20     xlPart = 2
30    xlByRows = 1


I had already checked out those values.
Remember, this works for the first pass.

Did you try the code?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
On a quick check, there is an issue with visibility, adding a workbook activation seems to help so in your test does it help?

Chris
Option Explicit

Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, _
  ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long


Function fnExcelTest(strFileNewName As String)

Dim objApp As Object, objAppBook As Object
Dim objXLApp As Object, objResultsSheet As Object
Dim xlPart As Integer, xlByRows As Integer

10    On Error GoTo fnExcelTest_ERR

'Set Up some Excel constants
20    xlPart = 2
30    xlByRows = 1

        '**Create an excel object, book object , and app object
40    Set objApp = CreateObject("Excel.Application")
50    Set objAppBook = GetObject("c:\ExcelTemplateTest.xls")
60    Set objResultsSheet = objAppBook.Worksheets(1)

      'For debugging, show the excel object and worksheets
70    objAppBook.Parent.Windows(objAppBook.Name).Visible = True
80    objAppBook.Parent.Visible = True
      objAppBook.Activate

      'Select a row
90    objResultsSheet.Rows("5:5").Select
      'Do a simple replace
100   objApp.Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    
      'Save it with the new name then open it
110   objAppBook.SaveAs strFileNewName

      'Close objects
120   objApp.Workbooks.Close
130   objApp.Quit
140   Set objApp = Nothing

   
fnExcelTest_EXIT:
      Dim lngRetShell As Long
150   lngRetShell = ShellExecute(0, "OPEN", strFileNewName, "", "", 0)


160   Exit Function

fnExcelTest_ERR:

170   MsgBox "The error " & Err & " occurred at line " & Erl & vbNewLine _
      & "ERROR: " & Err.Description
180         Stop
190    Resume fnExcelTest_EXIT


End Function

Open in new window

0
 
askolitsAuthor Commented:
Nope. Didn;t help.
0
 
LucasMS Dynamics DeveloperCommented:
Instead of:

objApp.Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Try:

Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
0
 
askolitsAuthor Commented:
lucas911:

"I get a compile error: Variable not defined."

That may work for early binding, but I need to have late binding.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The second time around you have already made the change in teh file so the search fails by design.  i.e. it isn't a failure of the code rather a failure of the concept to run the replacement operation a second time around.

Chris
0
 
askolitsAuthor Commented:
Note that the second time around, it has re-opened the template so essentiall a new doc.

I never tested it till now, but turns out it  doesn't matter if it's a second time around.

If you just open Excel with some randomn doc. Then run the code, just once, it also fails.

It actually does the row selection properly. The row highlights as expected. It just won't do the "replace" on a 2nd doc  when Excel is already open.



0
 
askolitsAuthor Commented:
More INfo:

Also, you don't even need a doc to open. Just the Excel shell. So if I open a doc, then close just the doc and leave the blank Excel app open, still fails.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
If the string i.e. "#N/A" is not found the script will fail.

It is the replace activity itself that fails.

i.e. if the string is there then it fails the second time, (as long as the file is not saved)

If the string is not there then it fails straight away.

The cause is the failure of replace operation when the string is not found and is perfectly logical.

Chris
0
 
askolitsAuthor Commented:
The template file does have those values in it.
Remember, the replace does in fact work except when the Excel app shell is open.

I hate to say this, but I posted the code and template file for people to try.

All of these suggestions except one would have answered themselves if the they were tried first.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I did try them, which is why I know what is causing the error.

The first time through it will work and the "n/a" is removed as part of that first run,  The second time through without closing the file then teh string has already been replaced and it fails to find it.

When you close the file without saving then the replaced string is there for the next first time through.

Chris
0
 
askolitsAuthor Commented:
The first time through, the template is opened. The #NAs are replaced.
And then the file is saved to another file name. In this way the original template sheet is not overwritten.
Not sure why the code is overwriting your version.

If you reopen the template, it shoud still have the "#N/As" in it.
As long as you didn't change the "objAppBook.SaveAs strFileNewName" line, it should not overwrite the template.

So your saying with the original code I posted, the template is being overwritten?
0
 
askolitsAuthor Commented:
I just downloaded the code off this thread to a different PC, with a different version of both Access and Excel. I ran the code and my template file was not overwritten. Something doesn't make sense.
0
 
askolitsAuthor Commented:
Bingo! You are da' man. (Or maybe your a woman?). Either way. Thanks alot!
0
 
LucasMS Dynamics DeveloperCommented:
"da'man" for sure ... doesn't this site have a woman avatar?
0
 
askolitsAuthor Commented:
"Woman avatar?"
Gee, that's a good question. I never noticed one before. I expect they do.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.