Error Handling in VBA and file counter 1

[darvistor]

I have a worksheet and user form that is used to import summary data from several data files (.CSV) and copy it to a tab in the main worksheet.

The user inputs where the files are stored, what type of file they want to open, the incremental number of the file, and the name of the tab where they want the data to go. After the data is copied, the filenumber increments and the next file is opened. Everything works fine until it comes to a file that doesnt exist. Here is the main piece of code(I took out some spaces so it didnt take up so much room) for the userform. The files have the following format depending on the file number:
p1_0001.csv, p1_0010.csv, p1_0100.csv

zero = "0"
zeroes = "00"
tzeroes = "000"
fileCount = 37

Do
filepath = path_txtbx.Text
filenum = filenum_txtbx.Value
fnamestor = fNamestor_cmbbx.Text
stabname = stabname_cmbbx.Text

    If (parameter_cmbbx.Text = "Weight") Then
        filepre = "G1"
    End If
    If (parameter_cmbbx.Text = "Hardness") Then
        filepre = "H1"
    End If
    If (parameter_cmbbx.Text = "Thickness") Then
        filepre = "D1"
    End If
    If (parameter_cmbbx.Text = "Diameter") Then
        filepre = "R1"
    End If
    If (parameter_cmbbx.Text = "Batch") Then
        filepre = "P1"
    End If
    If (fileCount < 1000) Then
        fpname = CStr(filepath + "\" + filepre + "_" + zero + filenum + ".csv")
        sname = CStr(filepre + "_" + zero + filenum)
    End If
    If (fileCount < 100) Then
        fpname = CStr(filepath + "\" + filepre + "_" + zeroes + filenum + ".csv")
        sname = CStr(filepre + "_" + zeroes + filenum)
    End If
    If (fileCount < 10) Then
        fpname = CStr(filepath + "\" + filepre + "_" + tzeroes + filenum + ".csv")
        sname = CStr(filepre + "_" + tzeroes + filenum)
    End If

 ChDir filepath
    With ActiveSheet.QueryTables.Add(Connection:="Text;" & fpname, _
        Destination:=Range("A1"))
        .Name = sname
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlNone
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileDecimalSeparator = ","
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False 'This errors when the file doesnt exist
        End With
        
BatchID = Range("B1")
Range("A8").Select
Selection.Value = BatchID
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
lastRowdest = Sheets(stabname).Cells(Rows.Count, "A").End(xlUp).Row + 1
Selection.Copy Sheets(stabname).Range("A" & lastRowdest)
Sheet3.Cells.Select
    Selection.ClearContents
    Selection.QueryTable.Delete 'This errors when the file doesnt exist
Sheet3.Range("A1").Select
fileCount = fileCount + 1
filenum_txtbx.Value = fileCount
Loop Until filenum = "100"
End Sub

I have two issues with the code that I can't figure out.
1. I need a way to set the initial filecount outside of the code so that the number of zeroes in the filename is correct. Currently, the user has to go into the code to make the filecount the same as the filenum they enter. For some reason, I can't link the filecount to the filenum text box because the loop doesn't work correctly.
2. When the code gets to a file that doesnt exist, the code fails on the .Refresh BackgroundQuery:=False. When a file doesnt exist, I need a way to skip to the end of the code where the filenumber increments and then continue the loop.

 

[rmikesmith]

You can greatly simplify generating the correct filename (including eliminating the If..Then checking of the filenumber size):

fpname = filepath & "\" & filepre & "_" [COLOR=red]Format(filenum,"0000")[/color] & ".csv"

Regards,
Mike

 

[rmikesmith]

I've re-written your procedure to
A) Make the loop increment properly
B) Update the filenum TextBox in the Userform each time through the loop
C) Remove variable assignments that don't change from inside the loop (i.e. those from Userform controls)
D) Explicitly check for existence of a particular file before trying to operate on it. Uses a simple function shown below

Sub YourProcedure()
Dim filepath As String
Dim filenum As Integer
Dim fnamestor As String
Dim fpname As String
Dim stabname As String


   filepath = path_txtbx.Text
   filenum = filenum_txtbx.Value
   fnamestor = fNamestor_cmbbx.Text
   stabname = stabname_cmbbx.Text

   If (parameter_cmbbx.Text = "Weight") Then
     filepre = "G1"
   ElseIf (parameter_cmbbx.Text = "Hardness") Then
     filepre = "H1"
   ElseIf (parameter_cmbbx.Text = "Thickness") Then
     filepre = "D1"
   ElseIf (parameter_cmbbx.Text = "Diameter") Then
     filepre = "R1"
   ElseIf (parameter_cmbbx.Text = "Batch") Then
     filepre = "P1"
   End If

   ChDir filepath
    
   Do
     fpname = filepath & "\" & filepre & "_" Format(filenum,"0000") & ".csv"
     If FileExists(fpname) Then
       With ActiveSheet.QueryTables.Add(Connection:="Text;" & fpname, _
         Destination:=Range("A1"))
         .Name = sname
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .TextFilePromptOnRefresh = False
         .TextFilePlatform = 437
         .TextFileStartRow = 1
         .TextFileParseType = xlDelimited
         .TextFileTextQualifier = xlNone
         .TextFileConsecutiveDelimiter = True
         .TextFileTabDelimiter = False
         .TextFileSemicolonDelimiter = True
         .TextFileCommaDelimiter = False
         .TextFileSpaceDelimiter = False
         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
         1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
         .TextFileDecimalSeparator = ","
         .TextFileTrailingMinusNumbers = True
         .Refresh BackgroundQuery:=False 'This errors when the file doesnt exist
       End With
     End If

     BatchID = Range("B1")
     Range("A8").Select
     Selection.Value = BatchID
     Range("A8").Select
     Range(Selection, Selection.End(xlToRight)).Select
     lastRowdest = Sheets(stabname).Cells(Rows.Count, "A").End(xlUp).Row + 1
     Selection.Copy Sheets(stabname).Range("A" & lastRowdest)
     Sheet3.Cells.Select
     Selection.ClearContents
     Selection.QueryTable.Delete 'This errors when the file doesnt exist
     Sheet3.Range("A1").Select

     [COLOR=green]'Not sure how fileCount and filenum are related
     'fileCount = fileCount + 1
     'filenum_txtbx.Value = fileCount[/color]
     filenum = filenum + 1
     filenum_txtbx.Value = filenum
   Loop Until filenum > 100

End Sub

Function FileExists(ByVal FName As String) As Boolean
   FileExists = (Dir(FName) <> "")
End Function

Hope this helps
Mike

 

[darvistor]

Cool. Thanks Mike. I will try this code tomorrow when I have access to the raw data files.

Just a couple questions/comments.

I originally specified filenum as a string because I couldnt figure out a way to create the filename/path correctly with the proper number of zeroes. I used filecount as an integer so that I could increment the count for the loop after the file contents were copied. I guess one of my problems was having the filenum set inside the loop from the text box.

1. Will the format command you used format(filenum,"0000") automatically put in the proper number of zeroes? In other words if it is number 23 it would be 0023 and if it is 100 it will be 0100? If so that is very helpful.

2. This may be a stupid question, but where does that Function code need to reside? Do I leave it right where you placed it? Sorry I dont use VBA very much.

3. What happens when the fname doesnt exist? Ideally, if it can't find the file it should try to find the next incremental file and keep going until it reaches the maximum value of the loop. I wasn't sure by looking at the code if this is going to happen or not.

 

[SkipVought]

"Will the format command you used format(filenum,"0000") automatically put in the proper number of zeroes?"

This is a needless question, since you could...

1. consult HELP on teh Format function.

2. try it out yourself.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[darvistor]

Yes skip, I could try the code, but I am at home and don't have access to the files. I figured I would ask the question since he went through the effort to change the procedure. I figured it would give me something positive to look forward to on a Monday.

I am new to VBA and the forums, try not being so hard on me.

 

[rmikesmith]

Some answers & suggestions:

Always a good practice to explicitly declare your variables, which I have done in my re-write of your procedure. You will notice that filenum is declared as an integer which can now be used in place of filecount as a loop counter. Also, VBA automatically converts between this numeric variable and the TextBox's value, which is a string (assuming a valid integer is entered into the TextBox).

Check out the Format function in the VBA Help for an explanation and a description of the many formatting codes you can use. The format string I used, "0000" displays a numeral, if present, or zero in each of the four places. So, it will pad the string with leading zeros, which is what you want.

The FileExists function could be placed in the Userform code module with your existing procedure, and it would work fine there, but since this is a general-purpose routine, I prefer a standard code module.

If a file doesn't exist, none of the data processing code is executed. I strongly suggest running through this procedure in debug, single-step mode so you can see what happens, particularly when a file doesn't exist (choose a starting filenum appropriately so that you aren't traversing the Do..Loop 10, 20, etc. times before a missing file comes up ).


Regards,
Mike

 

[SkipVought]

I'm not being hard on you at all. I did not critisize you for something that you did not know. Lord knows, the amount I know could fit in a thimble.

I was merely pointing out that there's a difference in asking a question that you legitimately do not know the answer to, and one that you could, yourself, find the answer to quite easlily.

Before asking any question, you should exaust your reference sources.

DIG, then ASK.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[rmikesmith]

Hey Skip,

Sure would like to see the size of that thimble!


Regards,
Mike

 

[SkipVought]

It's all relative!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[darvistor]

Just so I don't look like a complete newbie, I did declare all the variables in the code, I was trying to cut down on the amount of code I posted so I only posted the actual procedure.

I reviewed the help file for the format function. It works just as you intended. This will be a big help.

I put the Fileexists function in Module 1 and I stepped through the code this morning with the raw data files.

I put an On Error command after the If statement so that if the file doesn't exist it jumps to the bottom of the code, increments the filenum and continues the loop. This worked fine when only one file was missing in the series. When there is a larger gap between files (12, 13, 14, then 20) it stops at this section.

Selection.QueryTable.Delete

 

[PHV]

An error handler should use the Resume instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmikesmith]

There is no need to use error trapping this way. The function FileExists is specifically for this purpose. I looked back at my posted re-write and realized I placed the End If in the wrong location. It should encompass all of code manipulating the data transfer. Delete the End If from the following location:

      End With
     [highlight][s]End If[/s][/highlight]

     BatchID = Range("B1")

Add it here:

     Sheet3.Range("A1").Select

     [highlight]End If[/highlight]
     filenum = filenum + 1

The logic is that everything within the If..Then and End If will only execute if FileExists returns True. Otherwise this code is skipped, jumping directly to th incrementing of filenum.


Regards,
Mike

 

[darvistor]

Thanks for all the input/changes. The code seems to work perfectly. Now on to the next challenge of protecting the data.