saveAS

[Edje_007]

Hello , I hope that someone can help me whit this ?
I"m searching for days now !
"
I would like to save a file with the filename selected in cel "d14" and "h9 " ,but I want to choose the path myself .For example : c:\test\
It's every time a different path !
where must i put this code ? ' Application.GetSaveAsFilename '(or something)


Tnx


Code i'm using :

Dim fName As String, FileNm As String
'GET THE NEW FILENAME FROM THE CURRENT SHEET
FileNm = ActiveSheet.Range("D14").Value & ActiveSheet.Range("H9").Value
fName = ActiveWorkbook.Path & "\" & FileNm & ".xls"
Debug.Print fName
'IF THE FILE ALREADY EXISTS THEN DELETE IT ---------------------------------*
If Dir(fName) <> "" Then Kill fName
'COPY THE WORKSHEET
ThisWorkbook.ActiveSheet.Copy
'SAVE THE NEW WORKBOOK
ActiveWorkbook.SaveAs Filename:=fName
ActiveWorkbook.Close

 

[gaj104]

Hi,

This part of the code determines the path:

ActiveWorkbook.Path

I.e takes the current workbooks path.

If you want to change this, replace it for a variable i.e. thePath

fName = thePath & "\" & FileNm & ".xls"

Next question is where do you want to get the path from, another cell?

If another cell then use, placed above the code above.

thePath = ActiveSheet.Range("D14").Value & ActiveSheet.Range("xx").Value

Substitute xx for the cell address.

Hope it helps

 

[Edje_007]

Hoi Gaj ,

Thx for the quick responce !!
Sorry , but I don't undstand it ! Can you give me an example please ?
When i pusch the button , the filename = value from d14 and h9 .
I like to show the activewindow ( saveas) where i can choose the path.
On this moment the workbook will be saved on the same path i opened it !

Hope you can help me with this ?

Thx !!!

 

[gaj104]

Hi,

Adding the additional function will allow you to browse for a folder.

Give this a whirl and see if it works.

Sub Test()

Dim fName As String
Dim FileNm As String
Dim tPath As String


'GET THE NEW FILENAME FROM THE CURRENT SHEET
FileNm = ActiveSheet.Range("D14").Value & ActiveSheet.Range("H9").Value

tPath = BrowseForFolder("Please select your folder", 17)

    If tPath <> "" Then
    
        fName = tPath & "\" & FileNm & ".xls"
        
        Else
        
        MsgBox ("Nothing selected!")
        Exit Sub
        
    End If

Debug.Print fName

'IF THE FILE ALREADY EXISTS THEN DELETE IT ---------------------------------*
If Dir(fName) <> "" Then Kill fName

'COPY THE WORKSHEET
ThisWorkbook.ActiveSheet.Copy

'SAVE THE NEW WORKBOOK
ActiveWorkbook.SaveAs Filename:=fName
ActiveWorkbook.Close

End Sub

 
Private Function BrowseForFolder(Title As String, Optional RootFolder As Variant) As String
     
Dim Shell, Folder
Set Shell = CreateObject("Shell.Application")
Set Folder = Shell.BrowseForFolder(0, Title, 0, RootFolder)

    If (Not Folder Is Nothing) Then
        BrowseForFolder = Folder.Self.Path
    End If
     
End Function

Hope it helps

 

[Edje_007]

Hi Gaj

This is just what I need !!!!

Thank you very very mutch m8!



Ed