PDA

View Full Version : Solved: Setting the Directory - SaveAs



ChrisB
03-26-2007, 04:44 AM
Hi,

Am new to VBA so am unsure as to why the following code isn't doing as I would expect it to.

I'm trying to copy some worksheets and some VBA to a new workbook and then save the new file in a given location, however, when I run this it saves the new work book where the original file is.

Thanks in advance.


Dim Strdate As String
Strdate = Format(Now, "dd-mmm-yy h-mm-ss")
Dim Drive As String
Dim Directory As String
Drive = Sheets("VPF Parameters").Range("Drive")
Directory = Sheets("VPF Parameters").Range("Path")

Dim SaveFileName As String
SaveFileName = Sheets("VPF Parameters").Range("service")
Application.ScreenUpdating = False
On Error Resume Next
Kill ("C:\Module3.bas")
Workbooks("Easy Rota 4G-1c-20p VBA Version.xls").VBProject.VBComponents("Module3").Export ("C:\Module3.bas")
Workbooks("Easy Rota 4G-1c-20p VBA Version.xls").VBProject.VBComponents("Module3").Export ("C:\Module4.bas")
Sheets(Array("VPF Parameters", "Variablepay", "VPF Authorise")).Select
Sheets(Array("VPF Parameters", "Variablepay", "VPF Authorise")).Copy
Sheets("Variablepay").Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("A1").Select
Application.VBE.activeVBProject.VBComponents.Import ("C:\Module3.bas")
Application.VBE.activeVBProject.VBComponents.Import ("C:\Module4.bas")
Application.CutCopyMode = False
ActiveWorkbook.SaveAs SaveFileName & Strdate & ".xls", FileFormat:=xlNormal
ActiveWorkbook.Close

OBP
03-26-2007, 05:31 AM
I do not see anything in your code relating to the Path for the Save as operation, for instance
SaveFileName = Sheets("VPF Parameters").Range("service")
could be
SaveFileName = C:\Foldername\Sheets("VPF Parameters").Range("service")

Bob Phillips
03-26-2007, 05:33 AM
You don't seem to be using Directoty. I think your save should be



ActiveWorkbook.SaveAs Directory & "\" & _
SaveFileName & Strdate & ".xls", FileFormat:=xlNormal

ChrisB
03-26-2007, 05:51 AM
D'oh!
Cheers both.