Saving File as a .csv and then saving in a sprecific folder

[vman5781]

Good afternoon all

I want to be able to save a an specific sheet of a workbook as a .csv file and the have it save to specific folder path controlled by a cell (person's name ) located in the sheet.

Can anybody help with this?

 

[rpaulson]

I use this code all the time.

Sub Creat_Txt_File()

myFile = "c:\test\" & Range("A1") & ".csv"

If Dir(myFile) <> "" Then Kill myFile ' deletes file if it exists
data = ""
For r = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    lcol = Cells(r, Columns.Count).End(xlToLeft).Column
    For c = 1 To lcol
        data = data & Cells(r, c) & ","
    Next c
Next r
    
Open myFile For Append As #1 
Print #1 , data
Close #1 

End Sub

hth,

Ross

 

[vman5781]

Ross,

I am still trying to understand the VBA and not sure about your response I would like to use something like the below, with it being saved as .csv and have it saved in a folder by the mean of looking in to (cell # a1) and then



saving tSub SaveAs_Tab_Delimited()
Sheets("Export").Copy
ActiveSheet.SaveAs ThisWorkbook.Path & "\FR2900 0000433608__" & Format(Date, "mm-dd-yy") & ".txt", FileFormat:=xlTextWindows
ActiveWorkbook.Close SaveChanges:=False
End Subo the same folder as the name in that cell # then closed and saved.

John

 

[rpaulson]

here is the code you need:

if will create a tab separated text file in within a sub-folder name the value in Cell A1 that is created within the folder that your worksheet resides.

Sub Creat_Txt_File()
mypath = Application.ActiveWorkbook.Path & "" & [A1] & ""
myfile = mypath & "FR2900 0000433608__" & Format(Now, "mm-dd-yy") & ".txt"

If Dir(mypath, vbDirectory) = vbNullString Then MkDir mypath

If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists

For r = 1 To Range("A65536").End(xlUp).Row
    For c = 1 To Cells(r, 256).End(xlToLeft).Column
        data = data & Cells(r, c) & vbTab
    Next c
    data = data & vbCr
Next r
    
Open myfile For Append As #1 
Print #1 , data
Close #1 
data = ""

End Sub