SaveAs with auto complete file name doesn`t work

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
53
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!

Code:
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
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!

Code:
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
VBA Code:
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

BatCoder
Joined
Feb 28, 2002
Messages
1,343
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:
VBA Code:
.InitialFileName = Range("D1").Value + ".xls"

When you use
VBA Code:
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:

VBA Code:
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.
 
Solution

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
53
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:
VBA Code:
.InitialFileName = Range("D1").Value + ".xls"

When you use
VBA Code:
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:

VBA Code:
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 ?
 

Attachments

  • error.png
    error.png
    6.7 KB · Views: 4

otrava18

Board Regular
Joined
Feb 11, 2018
Messages
53

ADVERTISEMENT

Try this
VBA Code:
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 !
 

Attachments

  • error2.png
    error2.png
    5.2 KB · Views: 6

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343
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

Board Regular
Joined
Feb 11, 2018
Messages
53

ADVERTISEMENT

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

BatCoder
Joined
Feb 28, 2002
Messages
1,343
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

Board Regular
Joined
Feb 11, 2018
Messages
53
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 ?
 

Attachments

  • library.png
    library.png
    25.9 KB · Views: 4

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,776
Members
415,927
Latest member
vedasinternational

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