Open CSV files with variable name

[jeffcrtra]

Hey folks, greetings!

I have been dealing with this issue for a while already.

Because of my work I must create files and save them with variable name (date of creation), the format should be CSV. I made a macro that makes all the work for me, taking the name from a specific cell and saving the file with a variable name... wonderful!! BUT.... I have not been able to make the macro open the CSV with a variable name.

I need the macro to open to replace a character and close again, this part is succesful, but I just can't make it to open a variable filename.

What I have been doing is that the macro save it with a specific name, reopen and work it fine but at the end I manually gotta change the name.

Give me a help!! I really need this so bad! it is soooo time consuming. I'm using Excel 2007, by the way.
Thank you.

Here is the part of the code I'm using:

Sheets("Stock").Select
ChDir ThisWorkbook.Path
ThisWorkbook.SaveAs Filename:="Stk_2010-xx-xx.psv", _
FileFormat:=xlCSV, CreateBackup:=False

Sheets("Sales").Select
ChDir ThisWorkbook.Path
ThisWorkbook.SaveAs Filename:="Sls_2010-xx-xx.psv", _
FileFormat:=xlCSV, CreateBackup:=False

ChDir ThisWorkbook.Path
ThisWorkbook.SaveAs Filename:="back_up.xlsx", CreateBackup:=False

Workbooks.Open Filename:=ThisWorkbook.Path & "\Stk_2010-xx-xx.psv"

Cells.Replace What:="/", Replacement:="-", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Workbooks.Open Filename:=ThisWorkbook.Path & "\Sls_2010-xx-xx.psv"

Cells.Replace What:="/", Replacement:="-", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

 

[John_w]

Presumably the xx-xx in Stk_2010-xx-xx.psv are the month and day numbers with leading zeroes, but you haven't said what determines this. Is it today's date? If so, try:

"\Stk_2010-" & Format(Date, "mm-dd") & ".psv"

If not, what is the logic for determining xx-xx?

 

[jeffcrtra]

Hey John,
Thank you so much for replying.
Actually the date should is not today's, but a previous date. You see the customer is supposed to report the product`s stock every week, so this week he'll report last week`s stock, and so on.
I manage to place the date from a cell value int he file name but I'm still unable to reopen the csv file.

 

[djreiswig]

I'm guessing it might have something to do with the

ThisWorkbook.Path

as this can change depending on which workbook is open.