.csv to .xls with intelligence to scan name
Posted by Del on June 06, 2000 6:32 AM
Help, Tough one!
I have 2 .csv files that I will open.
A) is a Revenue Data Workbook
B) is a Minutes Data Workbook
I wish then to turn them into .xls files.
The problem is I want Excel to be able to tell which is which when converting them over. So A) = WB1.xls & B) = WB2.xls
So if A) contains "Revenue" or "Rev" in the Workbook title then it knows to change that to WB1.xls. Likewise with B) containing "Minutes" or "Min" then it knows to turn that into WB2.xls.
e.g. 22.05.min.csv
= WB2.xls
Can this be done?
Del.
Posted by Ivan Moala on June 06, 0100 11:36 PM
Del
This macro may do what you want ??
Sub ChangeCSV_xls()
Dim Awb As String
Dim Wb As Workbook
Dim Q As Integer
Const File1 = "WB1"
Const File2 = "WB2"
Awb = ThisWorkbook.Name
'ChDir "D:\csvtest"
On Error GoTo ErrH
For Each Wb In Application.Workbooks
Wb.Activate
If InStr(1, Format(ActiveWorkbook.Name, ">"), "REV") > 0 Then
Q = MsgBox("Save " & ActiveWorkbook.Name & " as .xls File ?", vbYesNo, "Save")
If Q <> vbNo Then
ActiveWorkbook.SaveAs FileName:=File1 & ".xls"
End If
ElseIf InStr(1, Format(ActiveWorkbook.Name, ">"), "MIN") > 0 Then
Q = MsgBox("Save " & ActiveWorkbook.Name & " as .xls File ?", vbYesNo, "Save")
If Q <> vbNo Then
ActiveWorkbook.SaveAs FileName:=File2 & ".xls"
End If
End If
Next
Windows(Awb).Activate
Exit Sub
ErrH:
MsgBox Err.Number & " : " & Err.Description
End Sub
Ivan
