I am trying to use and If statement to:
1. check and see if an old copy of a file exists in a folder,
2. and if so delete it,
3. then check to see if a new copy of a file exists in the same folder,
4. and if not save a copy.
I keep getting a Run-Time 13 error, Type Mismatch on the Dir statement, and endless searches have not yielded any results. Below I have pasted a sample of the code that I have tried (I have tried many different configurations with the Dir declaration, none have worked obviously). I have tried using variables where the full file path and file name are assigned to them, and also cutting them up into one variable that stores the path, and another variable that stores the file name.
Here is a sample:
Sub Update()
Dim directory1 As String, directory2 As String, directory3 As String, i As Integer, j As Integer, directory1 As String, directory2 As String
Dim oldMatrixPath As String, newMatrixPath As String, oldMatrix As String, newMatrix As String, path As String
myMonth1 = Application.InputBox(Prompt:="Input month of new matrix")
myDay1 = Application.InputBox(Prompt:="Input day of new matrix")
myYear1 = Application.InputBox(Prompt:="Input year of new matrix")
myMonth2 = Application.InputBox(Prompt:="Input month of old matrix")
myDay2 = Application.InputBox(Prompt:="Input day of old matrix")
myYear2 = Application.InputBox(Prompt:="Input year of old matrix")
directory1 = "http://some-long-url/" + myMonth1 + "%20" + myDay1 + "%20" + myYear1 + ".xls"
directory2 = "http://some-long-url/" + myMonth2 + "%20" + myDay2 + "%20" + myYear2 + ".xls"
Workbooks.Open (directory1)
Workbooks(myMonth1 + " " + myDay1 + " " + myYear1 + ".xls").Sheets("Sheet1").Activate
path = "C:\file\path\goes\here\"
newMatrixPath = "C:\file\path\goes\here\" & myMonth1 & " " & myDay1 & " " & myYear1 & ".xls"
newMatrix = myMonth1 + " " + myDay1 + " " + myYear1 + ".xls"
oldMatrixPath = "C:\file\path\goes\here\" & myMonth2 & " " & myDay2 & " " & myYear2 & ".xls"
oldMatrix = myMonth2 + " " + myDay2 + " " + myYear2 + ".xls"
If Dir(path & "\" & oldMatrix) Then
Kill oldMatrix
End If
If Dir(path & "\" & newMatrix) = Empty Then
ActiveWorkbook.SaveAs Filename:=newMatrix
End If
End Sub
Thank you
Bookmarks