User did not follow file naming convention... thus macro crashes

brncao

Board Regular
Joined
Apr 28, 2015
Messages
134
I told the user to name the files as This_Report_07-01-2015. Actually this is how they've been naming it so it wasn't my idea. Now the person is inserting Spaces instead of Underscores. It crashed my macro because it was expecting an underscore. Is there a way to deal with this? No, I'm not going to rename all the files.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

You can use the REPLACE function in your code to change the spaces into underscores like this


Code:
Dim Fnam As String
Fnam = Replace("This Report 07-1-2015", " ", "_")

so after replace evaluates the filename will look like this "This_Report_07-1-2015"
 

brncao

Board Regular
Joined
Apr 28, 2015
Messages
134
But how does that work? Wouldn't you get a File Not Found error in the first place? Before you can replace something, it needs to read what the "incorrect" file name is before any corrective actions can be taken. But that's a catch 22, because if the file name is incorrect, then you'll get a file not found error, and no corrective actions will be taken. Right now I have f = "\This_Report_"
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
How 'bout telling use what your code is supposed to do? There's almost always a workaround for idiots who can't follow arcane rules that can almost never be reliably (EDIT: or realistically) enforced.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

But how does that work? Wouldn't you get a File Not Found error in the first place? Before you can replace something, it needs to read what the "incorrect" file name is before any corrective actions can be taken. But that's a catch 22, because if the file name is incorrect, then you'll get a file not found error, and no corrective actions will be taken. Right now I have f = "\This_Report_"
The problem is that you did not post any of your code, so we do not know how it is attempting to you use the filename nor do we know how the filename is even getting into your running program... I can think of several ways your program could be coded and still "crash you macro"... unless you show us your code, all we can do is guess.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
This is one of the reason I don't like using set file names or folders. I use the
Code:
Application.FileDialog(msoFileDialogFilePicker)
to select the desired file(s) to run my macros on.
 

brncao

Board Regular
Joined
Apr 28, 2015
Messages
134

ADVERTISEMENT

Objective: Name the ranges in each Excel file. These excel files have different dates on them. A loop is created to loop through the files, name the ranges, and save & close them.

A simple Sub will pass the following arguments (starting and ending dates) to Auto_Define_Name
Code:
Option Explicit
Public Sub Auto_Define_Name(myStartingDate As Date, myEndingDate As Date)
Dim p As String, f As String
Dim d As Integer, totalDays As Integer
Dim startingYear As Integer, startingMonth As Integer, startingDay As Integer
Dim yyyymm As String, mmddyy As String
Dim rowNum As Integer
    p = "C:\XXXXX\XXXX\XXXXXXXXXXXXX\XXX " 'Path
    f = "\This_Report_" 'File name (partial)
    totalDays = DateDiff("d", myStartingDate, myEndingDate)
    startingYear = Year(myStartingDate)
    startingMonth = Month(myStartingDate)
    startingDay = Day(myStartingDate)
    
    For d = 0 To totalDays
        yyyymm = Format(DateSerial(startingYear, startingMonth, startingDay + d), "yyyy-mm")
        mmddyy = Format(DateSerial(startingYear, startingMonth, startingDay + d), "mm-dd-yy")
        
        Workbooks.Open Filename:=p & yyyymm & f & mmddyy & ".xlsx" 'This will return an error if the user puts in spaces because of f
        'The rest of the code... Going through a loop that increments the date by a day, etc.
     Next d
End Sub
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Is it possible to just loop through all of the files in the folder, or do you have files that shouldn't be touched?
 

brncao

Board Regular
Joined
Apr 28, 2015
Messages
134
Is it possible to just loop through all of the files in the folder, or do you have files that shouldn't be touched?

The latter unfortunately.

Jesus, now there's leading spaces in the file names :mad:
 

netuser

Active Member
Joined
Jun 19, 2015
Messages
420
Objective: Name the ranges in each Excel file. These excel files have different dates on them. A loop is created to loop through the files, name the ranges, and save & close them.

A simple Sub will pass the following arguments (starting and ending dates) to Auto_Define_Name
Code:
Option Explicit
Public Sub Auto_Define_Name(myStartingDate As Date, myEndingDate As Date)
Dim p As String, f As String
Dim d As Integer, totalDays As Integer
Dim startingYear As Integer, startingMonth As Integer, startingDay As Integer
Dim yyyymm As String, mmddyy As String
Dim rowNum As Integer
    p = "C:\XXXXX\XXXX\XXXXXXXXXXXXX\XXX " 'Path
    f = "\This_Report_" 'File name (partial)
    totalDays = DateDiff("d", myStartingDate, myEndingDate)
    startingYear = Year(myStartingDate)
    startingMonth = Month(myStartingDate)
    startingDay = Day(myStartingDate)
    
    For d = 0 To totalDays
        yyyymm = Format(DateSerial(startingYear, startingMonth, startingDay + d), "yyyy-mm")
        mmddyy = Format(DateSerial(startingYear, startingMonth, startingDay + d), "mm-dd-yy")
        
        Workbooks.Open Filename:=p & yyyymm & f & mmddyy & ".xlsx" 'This will return an error if the user puts in spaces because of f
        'The rest of the code... Going through a loop that increments the date by a day, etc.
     Next d
End Sub

Is there any part of your file name that will always be same ? If yes then it is very easy to do just use the following to get the file name and put in variable



Code:
Dim file As String
Dim File_Name As String

file = Dir$(path & "*" & PartialName & "*" & ".xlsx")  'replace Path and partial name with yours


'For multiple files you have to loop :

    File_Name = Dir(file, vbNormal)
     
    While File_Name <> ""
         'your code
    Wend
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top