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

Viewed 24

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("G3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
MsgBox ("Êã ÍÐÝ ßæÏ ÇáæÒä")

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

filesavename = Range("SupplierName")

Range("A2").Select
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
WorkRng.Copy
wb.Worksheets(1).Paste

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

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

Dim XPath As String
XPath = "C:\Users\mahmoud.senosy\Desktop\BatchAdd\"

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


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


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


    

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

End Sub

1 Answers

-delete the "saveFile = " from:

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

instead try:

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

Related