CODEWITHSUNDEEP
excelvbaexcel-formula
Brijesh
Brijesh

Reputation: 21

Get file name & path from cell reference

I have below code which will export worksheet as .CSV file. It will create new file with .CSV format with current file name & on current locations.

Want to modify it in a way it should pick file name to be save from cell N15 & it should get saved in pre defined path.

Sub ExportAsCSV()

Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
 End With        

Dim Change below to "- 4"  to become compatible with .xls files
MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, 
Len(CurrentWB.Name) - 5) & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, 
CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

Upvotes: 2

Views: 364

Answers (1)

Ronan Vico
Ronan Vico

Reputation: 605

Just change the value from your File Name Variable 

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook

    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Copy

    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
     End With        

    Dim Change below to "- 4"  to become compatible with .xls files
    MyFileName = CurrentWB.Path & "\" & Range("N15").value2

    Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, 
    CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    End Sub

Upvotes: 1

Related Questions