VBA code to write current workbook to file

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

Robert Crandal

Does anyone have any sample VBA code which writes
the current workbook into a new Excel file??
 
How can I copy a closed Excel file to a new Excel file??

I just realized that the only problem with the code below is that
all of that VBA code would also be copied into the new file, right??
I'm going to be copying LOTS of closed Excel files to new file
names, and I dont want the new files to have all that VBA code.
 
Hi Robert

You have mentioned "current workbook" and hence "Gary''s Student"'s response
is around copying the current workbook to a new workbook. If you are looking
at copying a closed workbook to a different name try the conventional
FileCopy method

Filecopy "c:\test1.xls","d:\test2.xls"

Or you can try the FileSystemObject CopyFile Method

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("c:\test1.xls") Then
fso.CopyFile "c:\test1.xls", "d:\destfolder\"
End If
 
And this....

Option Explicit

Sub SaveCurrentWorkBook()

Dim FileName As Variant

'Get the filename
'Returns False if the user cancels the dialog box.
FileName = Application.GetSaveAsFilename _
(FilterIndex:=5, Title:="Save As")

If FileName = False Then
MsgBox "No file was saved."
Exit Sub
End If

'Display full name and path of file selected
MsgBox "Your file will be saved as " & FileName

End Sub
 
Back
Top