Hi
I have created a macro in order to download a file (delimited format) in excel. I am using excel 2007.
Problem: When I clicked on button to download file, the file gets successfully downloaded and opens in a seperate excel workbook (New). but it gives me error after downloading the file. The prog runs perfectly till the green color codes but after that it gives me error. Please find the codes below and assist.
Required output: 1) it download the file from delimited format to excel
2) Must delete the first and the last row in the worksheet after downloading the file as the column header is in 2nd row (data starts from row2).
3) It automatically insert new sheets in the workbook and renamed them as mentioned in purple color code below.
4) then filter the data from the main sheet, copy and paste the data to the respective sheets names and delete the copied rows from the main sheet.
Please let me know if you have any questions.
Private Sub CommandButton1_Click()
ChDir "P:\SR Extracts"
Workbooks.OpenText Filename:="P:\SR Extracts\ISP406TC.NONCUSTO", Origin:= _
437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
, TrailingMinusNumbers:=True
Rows("1:1").Select
Selection.AutoFilterMode = True
Selection.Range("A1:x1").AutoFilter
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveWindow.TabRatio = 0.773
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "157 & 161"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "009 Cusip"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "DOB"
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "BOA"
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Life Conversion"
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Vetted"
Sheets("ISP406TC").Select
ActiveWindow.SmallScroll Down:=-12
Rows("1:1").Select
Selection.Copy
Sheets(Array("157 & 161", "009 Cusip", "DOB", "BOA", "Life Conversion", "Vetted")). _
Select
End Sub
I have created a macro in order to download a file (delimited format) in excel. I am using excel 2007.
Problem: When I clicked on button to download file, the file gets successfully downloaded and opens in a seperate excel workbook (New). but it gives me error after downloading the file. The prog runs perfectly till the green color codes but after that it gives me error. Please find the codes below and assist.
Required output: 1) it download the file from delimited format to excel
2) Must delete the first and the last row in the worksheet after downloading the file as the column header is in 2nd row (data starts from row2).
3) It automatically insert new sheets in the workbook and renamed them as mentioned in purple color code below.
4) then filter the data from the main sheet, copy and paste the data to the respective sheets names and delete the copied rows from the main sheet.
Please let me know if you have any questions.
Private Sub CommandButton1_Click()
ChDir "P:\SR Extracts"
Workbooks.OpenText Filename:="P:\SR Extracts\ISP406TC.NONCUSTO", Origin:= _
437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), _
Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1)) _
, TrailingMinusNumbers:=True
Rows("1:1").Select
Selection.AutoFilterMode = True
Selection.Range("A1:x1").AutoFilter
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveWindow.TabRatio = 0.773
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "157 & 161"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "009 Cusip"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "DOB"
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "BOA"
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Life Conversion"
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "Vetted"
Sheets("ISP406TC").Select
ActiveWindow.SmallScroll Down:=-12
Rows("1:1").Select
Selection.Copy
Sheets(Array("157 & 161", "009 Cusip", "DOB", "BOA", "Life Conversion", "Vetted")). _
Select
End Sub