You are here:

Excel/Open file2 if file1 not found

Advertisement


Question
Hi Tom,
Using Excel 2007 VBA
I thought this was working, until after further testing I discovered it doesn't. In the section where it tries to open a client file, if the file name in colA is not found, it should open  "template" (sTempbk) instead (and do stuff to it). It appears that this works only if the FileNotFound is at the top of colA (DataFile was sorted on colA). And it only works for that one FileNotFound, not subsequent files. (In this case it will not open sTempbk) Naturally, errors get thrown during processing when this happens. This is very odd. What's wrong with MyCode? Here's an abridged version:
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   'has template file
   Dim sPathN As String  'datafiles, clientsfiles
   Dim s As String, olds As String, news As String
   Dim icnt As Long, jcnt As Long, ii As Long
   Dim sTempBk As String
' Set the directory that has Index, Template here: make sure this ends with a back slash
   sPath = "C:\Users\MyDocs\"
   
   sTempBk = sPath & "template.xls"
'Open the dialog box to select the data file, which will be in a subdirectory of sPath
   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
'Sets the path = wherever file just opened was found
   sPathN = ActiveWorkbook.Path & "\"
   
' Sort on A then B, sorts thru ColS

'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
   news = cell.Value 'for when we do SaveAs at end
'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
'If this is a new client, open Template, SaveAs bkClient.shClient and work on it
   On Error Resume Next
       Set bkClient = Workbooks.Open(sPathN & s)
   On Error GoTo 0
   If bkClient Is Nothing Then
       Set bkClient = Workbooks.Open(sTempBk)
       bkClient.SaveAs sPathN & s
   End If
   Set shClient = ActiveSheet
'PROCESSING HERE***
'SaveAs Client file
   
   With bkClient
       .SaveAs sPathN & news & "Updated.xls"
       .Close
   End With

   olds = s
  
   End If    'ENDS THE IF FROM BEGIN THE LOOP
   Next
   End Sub

I sure hope you can help, this is driving me nuts.

Answer
Doug,

made some adjustments and tested it and it worked for me - including using the template.xls when the name was not found.  Had missing files in several places in the list.

<revision:  commented out my test path>

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   'has template file
   Dim sPathN As String  'datafiles, clientsfiles
   Dim s As String, olds As String, news As String
   Dim icnt As Long, jcnt As Long, ii As Long
   Dim sTempBk As String
' Set the directory that has Index, Template here: make sure this ends with a back slash
   sPath = "C:\Users\MyDocs\"
'   sPath = "D:\C_Drive\Data\"  My test path
   sTempBk = sPath & "template.xls"
'Open the dialog box to select the data file, which will be in a subdirectory of sPath
   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
'Sets the path = wherever file just opened was found
   sPathN = ActiveWorkbook.Path & "\"
' Sort on A then B, sorts thru ColS

'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
   news = cell.Value 'for when we do SaveAs at end
'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
'If this is a new client, open Template, SaveAs bkClient.shClient and work on it
   Set bkClient = Nothing
   On Error Resume Next
       Set bkClient = Workbooks.Open(sPathN & s)
   On Error GoTo 0
   If bkClient Is Nothing Then
       Set bkClient = Workbooks.Open(sTempBk)
       bkClient.SaveAs sPathN & s
   End If
   Set shClient = ActiveSheet
'PROCESSING HERE***
'SaveAs Client file
   
   With bkClient
       .SaveAs sPathN & news & "Updated.xls"
       .Close
   End With
   Set bkClient = Nothing
   olds = s
  
   End If    'ENDS THE IF FROM BEGIN THE LOOP
   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.