How do I open file if available and if not available move on to next file?

[Gazz_KW]

How do I open file if available and if not available move on to next file?

I need to open a set of specific .csv files and import the data within into a master report.

The problem I am having is that if there is not filename available (ie. master data did not produce the file) then the debug kicks in and the rest of the process grinds to a halt.

What I want it to do - Look for the filename, if it is available then open it and carry out the data import etc and then close the file when completed - If there is no file of that name, then skip the import and goto the next section of code looking for the next filename etc etc until data from all available files has been transferred.

The On Error Goto command does not allow the program to skip to the next set of commands like i think it should -


CODE I AM USING -

On Error GoTo err1 ' skips remaining code if there is a problem - e.g. file not available???????.
Sheets("Tasks Closed").Select ' slects the sheet we need to work on
Columns("A").Select ' select the columns that may have data in them
Selection.Clear 'clear out the old data

Workbooks.Open Filename:= _
"h:\temp\Tasks Closed.csv" 'open the file created by (Master Data)
Columns("A").Select ' select the columns may have data on them and copy it to the clip board
Selection.Copy
ActiveWindow.WindowState = xlMinimized ' minimize the window
Range("A1").Select 'select cell A1 on the sheet we are putting the data in (previously selected)
ActiveSheet.Paste ' and paste the data into it - this expands the selection and pastes all the copied data

' close window
Windows("Tasks Closed.csv").Activate
Application.CutCopyMode = False ' stops error about large amount of data in clipboard
ActiveWindow.Close

err1: 'label for error jump??????
' now do it all again for the next sheet
On Error GoTo err2 ' skips remaining code if there is a problem - e.g. file not available.
Sheets("Incidents Closed").Select
Columns("A:H").Select
Selection.Clear
Workbooks.Open Filename:= _
"h:\temp\Incidents Closed.csv"
Columns("A:H").Select

err2: 'label for error jump??????
' now do it all again for the next sheet
On Error GoTo err3 ' skips remaining code if there is a problem - e.g. file not available.
ETC ETC ETC



Any help would be most appreciated.

Cheers...........

 

[Norie]

For a start I would recommend not using On Error in the first place.

I would also suggest you don't use ActiveWindow and properly reference the workbooks you are opening.

If you want to check if a file exists the simplest method is probably Dir.

Dim wbCSV As Workbook
Dim strFileName As String
 
strFileName = "H:\temp\Tasks Closed.csv"
 
If Dir(strFileName) = "" Then
     Msgbox strFileName & " not located."
Else
     Set wbCSV = Workbooks.Open(strFileName)
     ' rest of code using wbCSV as a reference to the newly opened file
     ' etc
     wbCSV.Close
End If

 

[Gazz_KW]

Thanks for that Norie!

So if I am reading this correctly, All that Happens if the filename does not exist is that a message box pops up and informs the user and then the program will carry on to the next section looking for then next .csv file?

Would I then just name the newfile name in the next section of code?

ie.

strFileName = "H:\temp\Tasks Open.csv"

If Dir(strFileName) = "" Then
Msgbox strFileName & " not located."
Else
Set wbCSV = Workbooks.Open(strFileName)
' rest of code using wbCSV as a reference to the newly opened file
' etc
wbCSV.Close
End If

and so on.... (I have 10 files that data is taken from)

Cheers

 

[Norie]

If you mean can you reuse the variable strFilename then the answer is yes.