1

I'm totally new to Microsoft VBA and I'm getting trouble to fix an Excel macro.

The intention of this macro is that, when pressing a button, it automatically saves the active worksheet on a file but it is not working and I don't know why.

It seems correct to me.

Sub Save()
'
' Save Macro
'
Sheets("My_sheet").Select
    ChDir "C:\my_file"
    ActiveWorkbook.SaveAs Filename:=Range("B6"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    Sheets("My_sheet").Select
'
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
Rods2292
  • 665
  • 2
  • 10
  • 28
  • What isn't working? Do you get an error? What is in `Range("B6")`? – Comintern Sep 21 '16 at 22:15
  • I get an error which highlights "Sheets("My_sheet").Select" part of this macro and I can't save the worksheet. `Range("B6")` it's a cell where is written a name. This name needs to be the name of the saved file. – Rods2292 Sep 21 '16 at 22:19
  • 2
    I'm assuming you have a sheet named "My_sheet"? What is the error? – Comintern Sep 21 '16 at 22:20

1 Answers1

0

it seems to work fine as long as the sheet name is set as "My_Sheet", folder exist and file name is correct. You can try this to check if they are OK before saving:

Sub SaveMe()
Dim filename As String
'check if directory exist
If Dir("C:\my_file", vbDirectory) = "" Then
    'if not ask if it should be created and continued
    rspCreate = MsgBox("Directory doesn't exist, do you wish to create it and continue?", vbYesNo)
    If rspCreate = vbYes Then
        'create dir and carry on
        MkDir "C:\my_file"
    ElseIf rspCreate = vbNo Then
        'no selected, stop execution
        Exit Sub
    End If
End If

filename = Range("B6")
Sheets("My_sheet").Select
ChDir "C:\my_file"
'check if file name is valid
If FileNameValid(filename) Then
ActiveWorkbook.SaveAs filename:=Range("B6"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
Else
    MsgBox "Invalid file name, file not saved"
End If
Sheets("My_sheet").Select
End Sub


'check if vali file name is used in cell
Function FileNameValid(sFileName As String) As Boolean
Dim notAllowed As Variant
Dim i As Long
Dim result As Boolean
'list of forbidden characters
notAllowed = Array("/", "\", ":", "*", "?", "< ", ">", "|", """")
'Initial result = OK
result = True
For i = LBound(notAllowed) To UBound(notAllowed)
    If InStr(1, sFileName, notAllowed(i)) > 0 Then
    'forbidden character used
        result = False
        Exit Function
    End If
Next i
FileNameValid = result
End Function
Pav
  • 286
  • 1
  • 12