PDA

View Full Version : Solved: Copy a workbook



Djblois
12-16-2009, 02:04 PM
I can't seem to figure out something so simple as creating a copy of a workbook. I can copy a worksheet into a new workbook but not a whole workbook. Here is the code I am using:


If frmEmailWork.cbSendInOldFormat And Not Right(wbWorking.Name, 4) = ".xls" Then
filename = wbWorking.Name
Pos = InStrRev(filename, ".")
If Pos > 0 Then
filename = Left$(filename, Pos - 1)
End If
wbWorking.SaveAs "H:\" & Application.UserName & "\" & filename & ".xls", FileFormat:=xlNormal
End If
Set wbTemp = ActiveWorkbook


but I do not want it to save the current workbook as a new name - I want it to create a copy of the whole workbook then do a save as.

tpoynton
12-16-2009, 02:08 PM
wbWorking.SaveCopyAs ???

Djblois
12-16-2009, 02:27 PM
That doesn't work because it will not let me save it as a new File Format. I want to save it in a 2003 format instead of whatever file format it was originally in

mdmackillop
12-16-2009, 11:57 PM
I think you need to go through the various steps. (Using 2007)



Sub Test()
Dim wb As String
Dim wBook As Workbook
Dim FileName
FileName = "Test"
wb = "C:\Temp\zzz.xlsm"
ActiveWorkbook.SaveCopyAs wb
Set wBook = Workbooks.Open(wb)
wBook.SaveAs "C:\Temp\" & Application.UserName & "\" & FileName & ".xls", FileFormat:=xlNormal
wBook.Close
Kill wb
End Sub

Djblois
12-17-2009, 07:42 AM
Thank you - that is what I was suspecting but i was hoping there was a shorter option.