0

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 Answer 1

0

-delete the "saveFile = " from:

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

instead try:

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.