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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Joined
Jul 30, 2006
Messages
3,656
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

dialup

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

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,545
Members
417,151
Latest member
ChickenTenderer

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