PDA

View Full Version : Need explanation to save file as



lks55
04-01-2016, 05:06 AM
Hi at all

I have to write a macro were I create a new workbook and then the user has to save it in a folder of his choice. I figured out that this code works:

Sub AddNew()

Dim s As Variant

Workbooks.Add
s = Application.GetSaveAsFilename("Excel Workbook", "Excel Files (*.xlsm), *.xlsm")
ActiveWorkbook.SaveAs "WK00 - UK Total"

End Sub

The thing is that I don't understand it. Workbooks.add creates the new file. The a path will be saved in 's'. But how does the next command knows that my active file has to be saved in the path s. I don't see a relation.

Best Regards

Lukas

gmayor
04-01-2016, 06:04 AM
It doesn't. See the following

Dim s As String
Workbooks.Add
s = Application.GetSaveAsFilename("Excel Workbook", "Excel Files (*.xlsm), *.xlsm")
ActiveWorkbook.SaveAs FileName:=s, FileFormat:=xlOpenXMLWorkbookMacroEnabled

lks55
04-01-2016, 06:23 AM
it does

"Sub AddNew()

Dim s As Variant
Dim s5 As Long
Dim MyFile As String
Dim directory As String
Dim erow


MsgBox "Please open a file to show the path of the regions"
s = Application.GetOpenFilename("Excel Workbook (*.xls; *.xlsx; *.xlsm),*.xls; *.xlsx; *.xlsm")
s = Left(s, InStrRev(s, "\"))
MyFile = Dir(s)

Workbooks.Add
s = Application.GetSaveAsFilename("Excel Workbook", "Excel Files (*.xlsm), *.xlsm")
ActiveWorkbook.SaveAs "WK00 - UK Total"

Do While MyFile <> ""
Workbooks.Open (MyFile)

s5 = 4

Do While Cells(s5, 2) <> ""
s5 = s5 + 1
Loop

Range(Cells(4, 2), Cells(s5, 13)).Copy
ActiveWorkbook.Close

ActiveSheet.Paste
Loop


End Sub"

This is my code and it works. I have altered it now, as you said and it is even better thanks. But Could u explain the connection?