file does not exist

dialup

Board Regular
Joined
Jan 10, 2008
Messages
99
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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):

Code:
If Len(Dir("C:\myFolder\MyFile.txt"))>0 Then
  'file exists!  Take appropriate action!
Else
  'file doesn't exist - do something else!
End if
 
Upvote 0
ok here's the code - be kind - i have never learnt vba - everything i know has been learnt through here and google
Code:
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
 
Upvote 0

Forum statistics

Threads
1,219,005
Messages
6,145,706
Members
450,635
Latest member
Rookie3510

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
Back
Top