Save as .xls to folder specified by cell worksheet

[Hester's Dad]

Hello everyone. First post, and verrrry new to VBA/macros, so please be gentle. I've been searching the forums and can't seem to find the answer I'm looking for. What I have found isn't working when I try to add it.

I appropriated a bit of code which provides an input box for the date and saves the file as that was kindly supplied to another user by Leith Ross in this thread: http://www.excelforum.com/excel-prog...e-file-as.html. I made some tiny changes -- text in the input box, file path, that kind of thing) and ended up with:

Sub Button26_Click()
  Dim InputDate As Variant
  Dim FileType As String
  Dim myFileName As String
  Dim myFolder As String
  
    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:\Library Stats\Service desk"
    
EnterDate:
      InputDate = InputBox("Please enter date of Report mm/dd/yy")
      If InputDate = "" Then Exit Sub
    
      If Not IsDate(InputDate) Then
         MsgBox "The date you entered is not valid"
         GoTo EnterDate
      End If
    
      InputDate = " " & Replace(InputDate, "/", "-")
      ThisWorkbook.SaveAs myFolder & "\" & myFileName & InputDate & FileType
End Sub

This works perfectly as it is. What I would like to do, though, is send the file to a folder corresponding to the current month. Searching, I saw mkDir as an option, but I don't want new folders each time I save (all May worksheets should go in one May folder, &c) and I didn't know how to incorporate mkDir anyway, so I thought pre-creating the folders and adding a series of If statements would work. I wound up with something like this:

Sub Button26_Click()
  Dim InputDate As Variant
  Dim FileType As String
  Dim myFileName As String
  Dim myFolder As String
  
  If Worksheets("input sheet").Range("a71").value = 5 then
    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:\Library Stats\Service desk\May"
      else
   If Worksheets("input sheet").Range("A71").Value = 6 then
          FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:\Library Stats\Service desk\June"
        End If
        End if

And so forth, with a statement for each month.

When I run this, I get the input box, and it lets me enter the date, but then I get Error 1004, saying it can't find path C:\105264 (the number is made up; i reverted to the IF-less code) and to make sure it exists or isn't being used by another program. When I click debug, the line highlighted is:

ThisWorkbook.SaveAs myFolder & "\" & myFileName & InputDate & FileType

I don't see why adding the IFs should make a difference there, or what the problem is. Obviously there's something I'm not getting. If anyone could point me in the right direction, I'd be endlessly grateful.

-HD

[royUK]

How are you getting the numbers in A71? If you place the date there then you could us

Option Explicit

Sub Button26_Click()
    Dim InputDate As Variant
    Dim FileType As String
    Dim myFileName As String
    Dim myFolder As String
    Dim myMonth As String


    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myMonth = Month(Worksheets("input sheet").Range("A71").Value)
    myFolder = "J:\Library Stats\Service desk\" & myMonth
End Sub

If you place the number there then using select case would be a better approach, & don't duplicate unnecessary lines like setting the FileType for each number

Option Explicit

Sub Button26_Click()
    Dim InputDate As Variant
    Dim FileType As String
    Dim myFileName As String
    Dim myFolder As String
    Dim myMonth As String

    Select Case Worksheets("input sheet").Range("a71").Value
    Case 5: myMonth = "May"
    Case 6: myMonth = "June"
    Case 7: myMonth = "July"
        'etc
    End Select
    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myFolder = "J:\Library Stats\Service desk\" & myMonth

End Sub

[Hester's Dad]

Hi RoyUK, and thanks for the response.

I neglected to mention that the value for cell A71 is the number of the month derived from =NOW() and formatted to display m only. I also have a cell A5 that displays the month name, also from =NOW(), formatted mmmm.

I tried inserting both of your suggested codes (separately), and neither one works for me. In both cases when I try to run the macro I get the input box asking me for the date, and when I enter that, I get "Run time error '1004' Method 'SaveAs' of object '_workbook' failed." When I debug the first option, the line that's highlighted is still:

ThisWorkbook.SaveAs myFolder & "\" & myFileName & InputDate & FileType

. Hovering over each piece of that line, myFolder is J:\Library stats\Service desk\5, myFileName is Service Desk Report: , InputDate is 5-27-10, and FileType is .xls. I'd originally named my folders January, February, March, &c., so I changed them to numbers 1-12, and still got the same error.

When I tried the second option, I get the same error message, and the same line highlighted as a problem. This time, though, myFolder is J:\Library stats\Service desk. No month folder.

After each of the above options, I'm including the input date code, so for example,. the whole thing using your first option looks like:

Option Explicit

Sub Button26_Click()
    Dim InputDate As Variant
    Dim FileType As String
    Dim myFileName As String
    Dim myFolder As String
    Dim myMonth As String


    FileType = ".xls"
    myFileName = "Service Desk Report: "
    myMonth = Month(Worksheets("input sheet").Range("A5").Value)
    myFolder = "J:\Library Stats\Service desk\" & myMonth
    
EnterDate:
      InputDate = InputBox("Please enter date of Report mm/dd/yy")
      If InputDate = "" Then Exit Sub
    
      If Not IsDate(InputDate) Then
         MsgBox "The date you entered is not valid"
         GoTo EnterDate
      End If
    
      InputDate = " " & Replace(InputDate, "/", "-")
      ThisWorkbook.SaveAs myFolder & "\" & myFileName & InputDate & FileType
End Sub

Is there some problem with the second piece, or do the two halves not get along. Should I be derving my A71/A5 values differently?

Thanks,
-HD

[Hester's Dad]

After continuing to search the forums, I've drawn up a much simpler bit of code that looks like it should work, but for some reason, doesn't. Clearly I'm being thickheaded, but what is it I'm missing?

The new code should figure out the month for the folder and the date for the filename by itself:

Sub Button26_Click()
    Dim InputDate As Variant
    Dim FileType As String
    Dim myFileName As String
    Dim myFolder As String
    Dim myMonth As String


    FileType = ".xls"
    myFileName = "Service Desk Report: " & Format(Now(), "mmmm dd yyyy")
    myMonth = Format(Now(), "mmmm")
    myFolder = "J:\Library Stats\Service desk\" & myMonth
    
  ThisWorkbook.SaveAs myFolder & "\" & myFileName & FileType

End Sub

When I run it, though, I get error 1004: method 'Saveas" of object 'workbook' failed. The bad line is still this one:

ThisWorkbook.SaveAs myFolder & "\" & myFileName & FileType

. MyFolder is the right folder, myFileName is the right file name, and FileType is .xls. Is there another way to tell it to save as?

[Hester's Dad]

It's working now. A colleague pointed out that I had a colon in my file name, and it was balking at that. The working code is:

Sub Button26_Click()
    Dim InputDate As Variant
    Dim FileType As String
    Dim myFileName As String
    Dim myFolder As String
    Dim myMonth As String

    FileType = ".xls"
    myFileName = "Service Desk Report " & Format(Now(), "mmmm dd yyyy")
    myMonth = Format(Now(), "mmmm")
    myFolder = "J:\Library Stats\Service desk\" & myMonth
    
  ThisWorkbook.SaveAs myFolder & "\" & myFileName & FileType
   MsgBox "File saved succesfully."
End Sub

Thanks again,

-HD

[Marzia]

Hi there, please help me! I have such a headache! I know nothing about VBA and I need to work out a makro which I copied somewhere else. All I need to do is described in different ways in this forum but anyone of these codes I found seem to be suitable for me. My need is saving a xls file with a name from a cell and in a specific location in my computer. I'm using Mac and Excel 2004. The following code creates the file with the name I want and this is ok, but then, what it's happening is that the path where the file need to be saved is completely ignored! the file is saved in the first window that is open on the desktop. I don't even know if the path i put (//localhost...) is correct since everywhere I found examples on pc (C:\) and this is not helping me at all. Hope to receive some help. Thank you very much! Marzia

my code:

Sub salva()
Dim C As String
C = "" & Range("C3") & "-" & Range("J1").Value
directory = "/localhost/Volumes/Amministrazione/Amministrazione/SchedeLavoriClienti/CAVATORTA/2010"
ActiveWorkbook.SaveAs Filename:=C & ".xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub