file does not exist

[dialup]

Hello all

I have had so much help from this wonderful community so I had to bring my latest problem here.

I have a spreadsheet that imports backup files from an intranet 'tool' and uses the data for MI reporting.

The tool is new and has had days when it was out of action and so has left gaps in the back up files.

Because my spreadsheet opens the files in via a macro and follows a logic path to determin the names (date) of the files it needs to open and then copies the worksheet into my workbook, where the file does not exist i get an error.

without using an error handler is there any kind of 'if' statement that can refrence the fact that the file is not found - or is there code to search for the file before opening it so i can use an if statement before trying to open it?

 

[stanleydgromjr]

dialup,

Please post your macro code with code tags.


At the beginning of your posted code, enter the following without the quote marks:
["code"]


Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]


Have a great day,
Stan

 

[Richard Schollar]

If you pass the filename and path to the Dir function it will return an empty string if nothing is found (so you can test this):

If Len(Dir("C:\myFolder\MyFile.txt"))>0 Then
  'file exists!  Take appropriate action!
Else
  'file doesn't exist - do something else!
End if

 

[dialup]

ok here's the code - be kind - i have never learnt vba - everything i know has been learnt through here and google

Private Sub userform_initialize()
Application.ScreenUpdating = False
Dim year As String
Dim Day As String
Dim month As String
year = Sheet1.Range("a2").Text
Day = Sheet1.Range("a3").Text
month = Sheet1.Range("a4").Text
TextBox1.Text = year
TextBox2.Text = month
TextBox3.Text = Day
    Workbooks.Open Filename:="H:\ASC\GA Tool\System Output\Backup\Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00.csv"
    Sheets("Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00").Select
     Sheets("Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00").Copy After:=Workbooks("ea_reports.xls").Sheets(2)
    Windows("Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00.csv").Activate
    ActiveWindow.Close
     Sheets("Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00").Select
    ThisWorkbook.Sheets("Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00").Select
    ThisWorkbook.Sheets("Backup-" & TextBox1.Text & "-" & TextBox2.Text & "-" & TextBox3.Text & "-23-00").Name = TextBox3.Text & "-" & TextBox2.Text & "-" & TextBox1.Text
Dim R As Long
Dim C As Range
Dim rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.count > 1 Then
 Set rng = Selection
 Else
 Set rng = ActiveSheet.UsedRange.Rows
 End If
 For R = rng.Rows.count To 1 Step -1
 If Application.WorksheetFunction.CountA(rng.Rows(R).EntireRow) = 0 Then
 rng.Rows(R).EntireRow.Delete
 End If
 Next R
 
  TextDeleteC
  ListSheets
 TextBox4.Value = CDate(Now())
 TextBox4.Value = Format(TextBox4.Value, "dd/mmm/yyyy")
Sheet1.Range("b1") = TextBox4.Text
UserForm3.Hide 
End Sub

the start is because the filenames are in the format yyyy-mm-dd and no matter how i force the format on the spreadsheet - i can get it to look like yyyy-mm-dd vba sees it as yyyy/mm/dd which won't work as a file name because of the / so i write each part to a textbox and use the textbox.text and "-" in the filename

 

[dialup]

Richard - thank you for that code - works perfectly