You are here:

Excel/File not found, open template

Advertisement


Question
Hi Tom,
Using Excel 2007 VBA
Here’s an abbreviated version of my macro so far. It looks for the file name in colA, opens it as bkClient.shClient (from the same directory), does a bunch of processing to it, close/save, then on to the next file, and so on.
However, sometimes it is a new client, and the file name will not be found (NameOfFile=NameOfClient). When this happens, I need to open “Template.xls”, do the processing and save as “FileName.xls” (where the new file will actually be named = the file name which was not found in colA).
I think it would be preferable if “FileNotFound” happens, to open “Template.xls” and immediately SaveAs “NewFileName.xls” (actually whatever the macro couldn’t find in colA), allow the macro to try again and successfully find the file and do the processing on it. The macro would then just continue business as usual. But maybe you have a better idea. I’d appreciate any help you can give.

Sub MyCode()
   Dim bkData As Workbook
   Dim shData As Worksheet
   Dim bkClient As Workbook
   Dim shClient As Worksheet
   Dim r As Range, cell As Range
   Dim fName As Variant
   Dim sPath As String
   Dim s As String, olds As String
   Dim icnt As Long, jcnt As Long, ii As Long
' Set the directory here:
   sPath = "C:\Users\Documents\MyFiles\"    ' make sure this ends with a back slash
'Open the dialog box to select the data file
   ChDrive sPath
   ChDir sPath
   fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls?),*.xls?", _
     Title:="Select Datafile then click on Open", MultiSelect:=False)
   If TypeName(fName) = "Boolean" Then
     MsgBox "No file selected.  Exiting . . . "
     Exit Sub
   End If
'OPEN THE SELECTED DATA FILE
   Workbooks.Open fName
   Set bkData = ActiveWorkbook
   Set shData = ActiveSheet
'set the range where filenames are found
   Set r = shData.Range("A2", shData.Cells(shData.Rows.Count, "A").End(xlUp))
'set the vertical range for each file name in ColA
   icnt = 0
   For Each cell In r
   icnt = icnt + 1
   s = cell.Value & ".xls"   'grab a file name and add extension
'BEGIN THE LOOP
'jcnt=# rows for file
   If s <> olds Then
       ii = icnt
       jcnt = 1
         Do While r(ii).Value = cell.Value
         jcnt = r(ii).Row - cell.Row + 1
         ii = ii + 1
       Loop
   
'OPEN CLIENT FILE AND SET AS ACTIVE SHEET
   Set bkClient = Workbooks.Open(sPath & s)
   Set shClient = ActiveSheet

'***DO A BUNCH OF PROCESSING NOW TO shClient

'Close and save Client file
   bkClient.Close savechanges:=True
   olds = s
  
   End If
   Next
   
   End Sub

Answer
Doug,

Try it this way:  (code is untested so you may need to tweak it).

Sub MyCode()
   Dim bkData As Workbook
   Dim shData As Worksheet
   Dim bkClient As Workbook
   Dim shClient As Worksheet
   Dim r As Range, cell As Range
   Dim fName As Variant
   Dim sPath As String
   Dim s As String, olds As String
   Dim icnt As Long, jcnt As Long, ii As Long

   Dim sTempBk as String
   sTempBk = "C:\Users\Documents\Template.xls"

' Set the directory here:
   sPath = "C:\Users\Documents\MyFiles\"    ' make sure this ends with a back slash
'Open the dialog box to select the data file
   ChDrive sPath
   ChDir sPath
   fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls?),*.xls?", _
     Title:="Select Datafile then click on Open", MultiSelect:=False)
   If TypeName(fName) = "Boolean" Then
     MsgBox "No file selected.  Exiting . . . "
     Exit Sub
   End If
'OPEN THE SELECTED DATA FILE
   Workbooks.Open fName
   Set bkData = ActiveWorkbook
   Set shData = ActiveSheet
'set the range where filenames are found
   Set r = shData.Range("A2", shData.Cells(shData.Rows.Count, "A").End(xlUp))
'set the vertical range for each file name in ColA
   icnt = 0
   For Each cell In r
   icnt = icnt + 1
   s = cell.Value & ".xls"   'grab a file name and add extension
'BEGIN THE LOOP
'jcnt=# rows for file
   If s <> olds Then
       ii = icnt
       jcnt = 1
         Do While r(ii).Value = cell.Value
         jcnt = r(ii).Row - cell.Row + 1
         ii = ii + 1
       Loop
   
'OPEN CLIENT FILE AND SET AS ACTIVE SHEET
  On error resume Next
   Set bkClient = Workbooks.Open(sPath & s)
  On Error goto 0
  if bkClient is nothing then
     set bkClient  = Workbooks.Open(sTempBk)
     bkClient.SaveAs sPath & s
  end if
   Set shClient = ActiveSheet

'***DO A BUNCH OF PROCESSING NOW TO shClient

'Close and save Client file
   bkClient.Close savechanges:=True
   olds = s
  
   End If
   Next
   
   End Sub

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.