Requesting file names
 

Ladies & Gents,

I use a macro which opens 3 files (A_acc.csv, A_warn.csv, & A_reject.csv),
formats & combines the files, then splits & saves them to 3 workbooks called
1_A.xls, 2_A.xls & 3_A.xls. The csv file names change daily. Other than
having to open & save each file individually, I have no problems. I wish to
update the macro so that I enter the file name, the macro then uses that
name to open the files, and save the new ones. Does anyone have any hints
that could help me?


Thanks in advance.

Andrew

Requesting file names
 

Hi Andrew,

As far as I understood, you want to enter the name, which is in this
case "A" and Excel opens 3 files (A_xxx.csv), does its job,whatever it
is and saves 3 files eventually (X_A.xls).

I think following code would help you (with a little adaptation), if
you don't want to use an UserForm. I did not try it but it must work :

Sub subOpenAndSave()

On Error goto ErrorHandler

Dim xsWB1 as Workbook, xsWB2 as Workbook, xsWB3 as Workbook
Dim strFileName as String
strFileName = InputBox("Please enter filename with full path : ")
set xsWB1 = Workbooks.Open(strFileName & "acc.csv")
set xsWB2 = Workbooks.Open(strFileName & "warn.csv")
set xsWB3 = Workbooks.Open(strFileName & "reject.csv")

...
' Here happens what you want to do with them
...

xsWB1.SaveAs FileName:= "1_" & strFileName
xsWB2.SaveAs FileName:= "2_" & strFileName
xsWB3.SaveAs FileName:= "3_" & strFileName

'and if you want to close them :
'xsWB1.Close
'...

Exit Sub

ErrorHandler :
if Err.Number = 1004 then
msgbox "File not found...exiting"
else
msgbox "unknown error...exiting"
end if

End Sub


---
Message posted from http://www.ExcelForum.com/

Requesting file names
 

Tolag,

Thanks for that. I'll give it a try, and see how I go. I certainly like
the error handler; I've never used one in any of my macros before. I
certainly can have some fun with that. :-)


Thanks again.

"tolgag " wrote in message
...
Hi Andrew,

As far as I understood, you want to enter the name, which is in this
case "A" and Excel opens 3 files (A_xxx.csv), does its job,whatever it
is and saves 3 files eventually (X_A.xls).

I think following code would help you (with a little adaptation), if
you don't want to use an UserForm. I did not try it but it must work :

Sub subOpenAndSave()

On Error goto ErrorHandler

Dim xsWB1 as Workbook, xsWB2 as Workbook, xsWB3 as Workbook
Dim strFileName as String
strFileName = InputBox("Please enter filename with full path : ")
set xsWB1 = Workbooks.Open(strFileName & "acc.csv")
set xsWB2 = Workbooks.Open(strFileName & "warn.csv")
set xsWB3 = Workbooks.Open(strFileName & "reject.csv")

..
' Here happens what you want to do with them
..

xsWB1.SaveAs FileName:= "1_" & strFileName
xsWB2.SaveAs FileName:= "2_" & strFileName
xsWB3.SaveAs FileName:= "3_" & strFileName

'and if you want to close them :
'xsWB1.Close
'...

Exit Sub

ErrorHandler :
if Err.Number = 1004 then
msgbox "File not found...exiting"
else
msgbox "unknown error...exiting"
end if

End Sub


---
Message posted from http://www.ExcelForum.com/