Save ActiveWorkbook dilemma

[Vacuum Sealed]

Hi All

Could someone have a look at this code and give me a pointer as to where I
am going wrong and why the code halts on the .Save within the With Statement
please...

It should be saving to E: Drive 1st, then change directory to C: Drive and
do the same, ala, it is not....


Sub save_WB()

Dim E_FileSave As Workbook
Dim E_FilePath As String
Dim E_FileName As String

Dim C_FileSave As Workbook
Dim C_FilePath As String
Dim C_FileName As String

Dim FileExtStr As String
Dim FileFormatNum As Long

Set E_FileSave = ActiveWorkbook

E_FilePath = "E:\Work_Stuff\"
E_FileName = E_FileSave.Name
FileExtStr = ".xls"

With E_FileSave
.SaveAs E_FilePath & E_FileName & FileExtStr,
FileFormat:=FileFormatNum
End With

Set C_FileSave = ActiveWorkbook

T_FilePath = "C:\Other Work_Stuff\"
T_FileName = C_FileSave.Name
FileExtStr = ".xls"

With C_FileSave
.SaveAs C_FilePath & C_FileName & FileExtStr,
FileFormat:=FileFormatNum
End With

End Sub

TIA
Mick.

 

[Rick Rothstein]

I don't see where you are setting a value for the FileFormatNum variable
that you are passing in to the FileFormat argument. That means FileFormatNum
is 0 when you call the .SaveAs method... none of the possible built-in VB
constants for this argument equate to a value of 0, so I'm guessing that is
your problem.

Rick Rothstein (MVP - Excel)

 

[Vacuum Sealed]

Thx for pointing that out Rick

I changed the code to this, but it only runs thru the 1st With and halts on
the 2nd, again the .SaveAs:

Sub save_WB()

Dim E_FileSave As Workbook
Dim E_FilePath As String
Dim E_FileName As String

Dim C_FileSave As Workbook
Dim C_FilePath As String
Dim C_FileName As String

Set E_FileSave = ActiveWorkbook

E_FilePath = "E:\Work_Stuff\"
E_FileName = E_FileSave.Name

With E_FileSave
.SaveAs E_FilePath & E_FileName
End With

Set C_FileSave = ActiveWorkbook

T_FilePath = "C:\Other Work_Stuff\"
T_FileName = C_FileSave.Name

With C_FileSave
.SaveAs C_FilePath & C_FileName
End With

End Sub

Cheers

 

[Vacuum Sealed]

Sorry Rick

Don't bother as I am a dill....

My Bad, as I actually have to have a directory named just that in the 2nd
With....Doh.....lol...

Mick.