SaveAs with auto complete file name doesn`t work

[otrava18]

Hello all,

I have tried to do a button with save as dialog box and autocomplete the file name based on a cell value. The cell value have also a path (C:\Users\user\Desktop\02.11.2020 - name). I tried more solutions but i couldn`t find a good one. The below code work partial. It give me the SaveAs dialog box and is also autocomplete like i want but if i press save, nothing is happened. The file is not saved. Could you give me an advise or a solution ?

Thank you very much!

Sub Copy_Fara_formule_1()
Dim i As Integer
Worksheets("LL").Range("A1:J178").Copy
Workbooks.Add

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

For i = 250 To 7 Step -1
If Cells(i, 6).Value = 0 Then Rows(i).Delete
Next i

Dim saveDialog As FileDialog
     Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
     With saveDialog
        .InitialFileName = Range("D1").Value + ".xls"
        .Show
    End With
End Sub

 

[Trixterz]

Hello all,

I have tried to do a button with save as dialog box and autocomplete the file name based on a cell value. The cell value have also a path (C:\Users\user\Desktop\02.11.2020 - name). I tried more solutions but i couldn`t find a good one. The below code work partial. It give me the SaveAs dialog box and is also autocomplete like i want but if i press save, nothing is happened. The file is not saved. Could you give me an advise or a solution ?

Thank you very much!

Sub Copy_Fara_formule_1()
Dim i As Integer
Worksheets("LL").Range("A1:J178").Copy
Workbooks.Add

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

For i = 250 To 7 Step -1
If Cells(i, 6).Value = 0 Then Rows(i).Delete
Next i

Dim saveDialog As FileDialog
     Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
     With saveDialog
        .InitialFileName = Range("D1").Value + ".xls"
        .Show
    End With
End Sub

Try this

Sub Copy_Fara_formule_1()
Dim i As Integer
Worksheets("LL").Range("A1:J178").Copy
Workbooks.Add

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

For i = 250 To 7 Step -1
If Cells(i, 6).Value = 0 Then Rows(i).Delete
Next i

Dim saveDialog As FileDialog
     Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
     With saveDialog
        .InitialFileName = Range("D1").Value + ".xls"
        .Show
    End With
    Application.ThisWorkbook.SaveAs = saveDialog.InitialFileName
End Sub

 

[smozgur]

You need to use the .Execute method after the .Show method in your code.

As a side suggestion, try to use objects instead using the default object in the context. Take a look at this code line:

.InitialFileName = Range("D1").Value + ".xls"

When you use

Range("D1").Value

without a parent object, which is supposed to be a worksheet object here, then it will refer to the active worksheet. I do understand that D1 cell is in the copied range, so your code works since that cell is also copied to the new worksheet. However, D1 might be in another worksheet in your project, or you might be pasting the range starting from a different cell instead of A1. Then, you'll be trying to fix a new problem.

Instead of that, set the source worksheet where D1 is placed, and use it. Something like this:

Sub Copy_Fara_formule_1()
Dim sht As Worksheet
Dim saveDialog As FileDialog
Dim i As Integer

    Set sht = ActiveSheet ' This could be or not the same sheet - "LL"
    ThisWorkbook.Worksheets("LL").Range("A1:J178").Copy

    Workbooks.Add

    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

    For i = 250 To 7 Step -1
        If ActiveSheet.Cells(i, 6).Value = 0 Then ActiveSheet.Rows(i).Delete
    Next i

    Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
    With saveDialog
       .InitialFileName = sht.Range("D1").Value + ".xls"
       .Show
       .Execute
    End With
  
End Sub

It can be also done by using a string variable for the workbook name, and set at the beginning before adding a new worksheet, but I always prefer working with parent object.
Just as a suggestion.

 

[otrava18]

You need to use the .Execute method after the .Show method in your code.

As a side suggestion, try to use objects instead using the default object in the context. Take a look at this code line:

.InitialFileName = Range("D1").Value + ".xls"

When you use

Range("D1").Value

without a parent object, which is supposed to be a worksheet object here, then it will refer to the active worksheet. I do understand that D1 cell is in the copied range, so your code works since that cell is also copied to the new worksheet. However, D1 might be in another worksheet in your project, or you might be pasting the range starting from a different cell instead of A1. Then, you'll be trying to fix a new problem.

Instead of that, set the source worksheet where D1 is placed, and use it. Something like this:

Sub Copy_Fara_formule_1()
Dim sht As Worksheet
Dim saveDialog As FileDialog
Dim i As Integer

    Set sht = ActiveSheet ' This could be or not the same sheet - "LL"
    ThisWorkbook.Worksheets("LL").Range("A1:J178").Copy

    Workbooks.Add

    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
    ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

    For i = 250 To 7 Step -1
        If ActiveSheet.Cells(i, 6).Value = 0 Then ActiveSheet.Rows(i).Delete
    Next i

    Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
    With saveDialog
       .InitialFileName = sht.Range("D1").Value + ".xls"
       .Show
       .Execute
    End With
 
End Sub

It can be also done by using a string variable for the workbook name, and set at the beginning before adding a new worksheet, but I always prefer working with parent object.
Just as a suggestion.

Hello smozgur,

I have tried your code and i receive an error. I put a picture with this error. In debug i find the command .execute in yellow. Until there everything work. Have you an idea about this ?

 

[otrava18]

Try this

Sub Copy_Fara_formule_1()
Dim i As Integer
Worksheets("LL").Range("A1:J178").Copy
Workbooks.Add

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats

For i = 250 To 7 Step -1
If Cells(i, 6).Value = 0 Then Rows(i).Delete
Next i

Dim saveDialog As FileDialog
     Set saveDialog = Application.FileDialog(msoFileDialogSaveAs)
     With saveDialog
        .InitialFileName = Range("D1").Value + ".xls"
        .Show
    End With
    Application.ThisWorkbook.SaveAs = saveDialog.InitialFileName
End Sub

Hello Trixterz,

I have tried your code and i receive an error. Have you an idea about this ?

Thank you for your help !

 

[smozgur]

Hello smozgur,

I have tried your code and i receive an error. I put a picture with this error. In debug i find the command .execute in yellow. Until there everything work. Have you an idea about this ?

It doesn't make sense. I don't think it is version related (but it could be great if you could go to your profile and set your Office and operating system versions).
Could you please check your project references (VBE->Tools->References), and make sure there is no library with MISSING prefix?

I assume you are not trying to save somewhere read-only or somewhere you don't have access privileges.

 

[otrava18]

It doesn't make sense. I don't think it is version related (but it could be great if you could go to your profile and set your Office and operating system versions).
Could you please check your project references (VBE->Tools->References), and make sure there is no library with MISSING prefix?

I assume you are not trying to save somewhere read-only or somewhere you don't have access privileges.

You have 100% right ! I have tried on other PC and is working. I will search why i receive an error on the first one. Is my mistake because i didn`t update my profile. I use Office 365 ProPlus and Windows 10 on both, but is strange because one is working one not. I need to say Thank you very much for your help and time !

 

[smozgur]

You have 100% right ! I have tried on other PC and is working. I will search why i receive an error on the first one. Is my mistake because i didn`t update my profile. I use Office 365 ProPlus and Windows 10 on both, but is strange because one is working one not. I need to say Thank you very much for your help and time !

You're welcome. Glad to hear it helps.

As I mentioned, if you don't have any MISSING libraries in that computer, which I believe you do and you need to remove that reference in that case, then it could be a read-only network path or something about folder permissions that you are trying to save the file.

 

[otrava18]

You're welcome. Glad to hear it helps.

As I mentioned, if you don't have any MISSING libraries in that computer, which I believe you do and you need to remove that reference in that case, then it could be a read-only network path or something about folder permissions that you are trying to save the file.

On both i have the same libraries. The new file is saved on desktop and i have rights and is not read-only. Maybe i need to use all libraries on laptop ?

 

[smozgur]

Maybe i need to use all libraries on laptop ?

No, there is no additional library to use the FileDialog property and its Execute method.

What about the file name? You mentioned you have the full path in D1. Have you tried by using the file name only?

This is kind of a try - catch issue since it works in the other computer but this one. Not likely but it could be even a corrupted library or something like that you might need to repair the Office installation. I am not saying this is the case, I am just saying I saw that kind of cases.