[SOLVED] Specifying file name when saving using VBA in excel returning a blank


This Content is from Stack Overflow. Question asked by Nasser Ramsis

I am trying to get this code to work but when the save dialogue opens the suggested file name is blank. I traced the variable “saveFile” in the locals window and it’s false instead of the string value. I’m not sure why.

Simply, I want the file name to be the suppliername followed by the word batch add and then today’s date.

Thanks is advance

Sub SaveToExcelFile()

On Error GoTo 1

Dim wb As Workbook
Dim filesavename As String
Dim saveFile As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Range(Selection, Selection.End(xlDown)).Select
MsgBox ("Êã ÍÐÝ ßæÏ ÇáæÒä")

Range(Selection, Selection.End(xlDown)).Select
MsgBox ("Êã ÍÐÝ ßæÏ ÇáÍÌã")

filesavename = Range("SupplierName")

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Set WorkRng = Application.Selection

def = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wb = Application.Workbooks.Add
Application.SheetsInNewWorkbook = def

daytouse = Day(Date)
monthtouse = Month(Date)
yeartouse = Year(Date)

Dim dateasstring As String
dateasstring = daytouse & "." & monthtouse & "." & yeartouse

Dim XPath As String
XPath = "C:Usersmahmoud.senosyDesktopBatchAdd"

filesavename = XPath & filesavename & " Batch Add " & dateasstring

saveFile = Application.GetSaveAsFilename(filesavename, fileFilter:="Excel Workbooks (*.xlsx),*.xlsx")

wb.SaveAs Filename:=saveFile
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True


Done: Exit Sub
1: MsgBox “ÇáÍÝÙ ÝÔá :)”

End Sub


-delete the "saveFile = " from:

saveFile = Application.GetSaveAsFilename(filesavename, fileFilter:="Excel Workbooks (.xlsx),.xlsx")

instead try:

Application.GetSaveAsFilename(filesavename, fileFilter:="Excel Workbooks (.xlsx),.xlsx")

This Question was asked in StackOverflow by Nasser Ramsis and Answered by user16099339 It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?