Macro Code Modification

[zaska]

Good Evening All.

I have a problem with the recorded macro code.

ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\cm08APR2011bhav.csv"

ChDir "E:\Macros\Output"
    ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\cm08APR2011bhav.txt", FileFormat:=xlCSV _
        , CreateBackup:=False

I cannot use this Macro Code The next day because the Input csv file name changes. to " cm09APR2011bhav.csv "

How can i modify this code so that i can use the recorded macro daily irrespective of the date

 

[VoG]

Try

ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\cm" & Format(Date, "ddmmmyyyy") & "bhav.csv"

ChDir "E:\Macros\Output"
    ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\cm" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV _
        , CreateBackup:=False

 

[RSXchin]

Vog beat me, but at least we gave the same answer :P

ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\cm" & format(now, "ddmmmyyyy" & "bhav.csv"
 
ChDir "E:\Macros\Output"
ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\" & format(now, "ddmmmyyyy" & "bhav.txt", FileFormat:=xlCSV _
, CreateBackup:=False

 

[zaska]

Thank you Sir,

It worked for me and it saved lot of time....

Is it possible to refer the date to a cell in Excel sheet1

For Example i have the input data for two days then this code will not work..




ChDir "E:\Macros\Input" Workbooks.Open Filename:="E:\Macros\Input\cm" & Format(Date, "ddmmmyyyy") & "bhav.csv" ChDir "E:\Macros\Output" ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\cm" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV _ , CreateBackup:=False</pre>
The Input Data has the Following Files...which change daily..

cm08APR2011bhav
EQ080411
fo08APR2011bhav
MTO_08042011
SCBSEALL0804

The date format is not the same for all the input files...

How do i accomplish my task refereing the date in excel...

Thank you for your kind support

Have a nice day

 

[VoG]

Try like this

ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\cm" & Format(Sheets("Sheet1").Range("A1").Value, "ddmmmyyyy") & "bhav.csv"

 

[zaska]

Sir,

It worked for the First Input file...but the date format of second file is different so it didn't accept the second input file.

I got subscript out of range error message..


The other Input Files..

MTO_08042011.DAT

EQ080411.csv

EQ080411.csv

fo08APR2011bhav.csv

Thank u

 

[VoG]

For the DAT file

Format(Sheets("Sheet1").Range("A1").Value, "ddmmyyyy")

For the other two

Format(Sheets("Sheet1").Range("A1").Value, "ddmmyy")

 

[zaska]

Sir,

I tried that already....thank u

but still i am getting subscript out of range Error

Workbooks.OpenText Filename:="E:\Macros\Input\MTO_" & Format(Sheets("Sheet1").Range("A1").Value, "ddmmyyyy") & ".DAT", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True

 

[VoG]

Try adding this line before that. does the file name look correct?

MsgBox "E:\Macros\Input\MTO_" & Format(Sheets("Sheet1").Range("A1").Value, "ddmmyyyy") & ".DAT"

 

[zaska]

Yes the File name is correct and all the four input files worked with your first code...

I think i should also refer the workbook name which is NSE Converter..

Can you add the worksheet name in the code instead of sheets..


"NSE Converter"