saveAS

Edje_007

New Member
Joined
Oct 18, 2006
Messages
9
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
Hi,

This part of the code determines the path:

Code:
ActiveWorkbook.Path

I.e takes the current workbooks path.

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

Code:
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.

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

Substitute xx for the cell address.

Hope it helps
 

Edje_007

New Member
Joined
Oct 18, 2006
Messages
9
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

Well-known Member
Joined
Nov 9, 2002
Messages
864
Hi,

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

Give this a whirl and see if it works.

Code:
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
 

Forum statistics

Threads
1,136,651
Messages
5,677,004
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top