save file via Marco

T

Tom

Hi,

I'm using this macro :

Dim vFilename
vFilename = "Export Q 1.0 " & Worksheets
("Export").Range("A2").Value & ".xls"
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:=vFilename,
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

The problem is that the file is not allways saved on c:

Is it possible to save it allways on C:
Or is it even possible to save in on C:\XXX.
Can be checked If c:\XXX exists ? If not create C:\XXX ?

Thanks !
Tom
 
B

Bob Phillips

Tom,

Here is one way

Sub CheckFolder()
Dim vFilename
Dim sFolder
Dim fso
sFolder = "C:\NewDir"
If Not FolderExists(sFolder) Then
MkDir sFolder
End If
vFilename = sFolder & "\" & _
"Export Q 1.0 " & _
Worksheets("Export").Range("A2").Value & ".xls"
ActiveWorkbook.SaveAs Filename:=vFilename, _
FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub

'-----------------------------------------------------------------
Function FolderExists(Folder) As Boolean
'-----------------------------------------------------------------
Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder <> "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
FolderExists = True
End If
End If
End Function




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom

Bob,

I understand the first part.
Does the first part works if the folfer allready exists ?

I don't understand the second part
sFolder = Dir(Folder, vbDirectory) is here Folder the
name of the folfer ?

What does it do ?
Has it to be run before the first part ?
PS : I don't know if the folder allready exists on the
PC's that are using this XLS file and so this marco ...
So it has to work wether it exists or not.

Thanks !
Tom
 
T

Tom

I tested this :
Dim sFolder
Dim fso
sFolder = "C:\ABC"
If Not FolderExists(sFolder) Then
MkDir sFolder
End If
End Sub

But it doesn't work
FolderExists : Sub or function not defined

Tom
-----Original Message-----
Bob,

I understand the first part.
Does the first part works if the folfer allready exists ?

I don't understand the second part
sFolder = Dir(Folder, vbDirectory) is here Folder the
name of the folfer ?

What does it do ?
Has it to be run before the first part ?
PS : I don't know if the folder allready exists on the
PC's that are using this XLS file and so this marco ...
So it has to work wether it exists or not.

Thanks !
Tom

-----Original Message-----
Tom,

Here is one way

Sub CheckFolder()
Dim vFilename
Dim sFolder
Dim fso
sFolder = "C:\NewDir"
If Not FolderExists(sFolder) Then
MkDir sFolder
End If
vFilename = sFolder & "\" & _
"Export Q 1.0 " & _
Worksheets("Export").Range("A2").Value & ".xls"
ActiveWorkbook.SaveAs Filename:=vFilename, _
FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub

'-------------------------------------------------------
Click to expand...
- -
---------
Dim sFolder As String
On Error Resume Next
sFolder = Dir(Folder, vbDirectory)
If sFolder <> "" Then
If (GetAttr(sFolder) And vbDirectory) = vbDirectory Then
FolderExists = True
End If
End If
End Function




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
.
Click to expand...
 
B

Bob Phillips

Tom,

You have to add both bits of code to your workbook, as the first uses the
second.

If the folder exists it works fine, using that folder. If it doesn't it
creates it.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top