help on a macro

I recorded macro that imports a text file and adds headers
to the columns. When I run the macro it opens a new workbook
and inserts the text file.

Is is possible to add code that will copy the sheet from the
new workbook into the original or just insert the imported
file as a new sheet in the original workbook?

Sub LoadBOReport()
'
' Macro1 Macro
' Macro recorded 3/15/2005
'
' Keyboard Shortcut: Ctrl+r
'
Workbooks.OpenText
Filename:="C:\DailyBackOrders\BackOrders.txt", Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 2 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), 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)), _
TrailingMinusNumbers:=True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Item Nbr"
Range("B1").Select
ActiveCell.FormulaR1C1 = "CO"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Desc"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Class"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Unit"
Range("F1").Select
ActiveCell.FormulaR1C1 = "BO Qty"
Range("G1").Select
ActiveCell.FormulaR1C1 = "S Qty"
Range("H1").Select
ActiveCell.FormulaR1C1 = "OW"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Pick Nbr"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Cust Nbr"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Dept"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Dept Nmae"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Entered"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Due Date"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Cust PO"
Range("P1").Select
ActiveCell.FormulaR1C1 = "WMP PO"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Order Date"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Due Date"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Inv Date"
End Sub

Assuming you start the macro with the top, left cell selected that you want
to paste to, try this:

Sub LoadBOReport()
Dim PasteToCell As Range
Set PasteToCell = ActiveCell
Application.ScreenUpdating = False
Workbooks.OpenText Filename:="C:\DailyBackOrders\BackOrders.txt",
Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 2 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
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)), _
TrailingMinusNumbers:=True
Rows("1:1").Insert
Range("A1").Value = "Item Nbr"
Range("B1").Value = "CO"
Range("C1").Value = "Desc"
Range("D1").Value = "Class"
Range("E1").Value = "Unit"
Range("F1").Value = "BO Qty"
Range("G1").Value = "S Qty"
Range("H1").Value = "OW"
Range("I1").Value = "Pick Nbr"
Range("J1").Value = "Cust Nbr"
Range("K1").Value = "Dept"
Range("L1").Value = "Dept Name"
Range("M1").Value = "Entered"
Range("N1").Value = "Due Date"
Range("O1").Value = "Cust PO"
Range("P1").Value = "WMP PO"
Range("Q1").Value = "Order Date"
Range("R1").Value = "Due Date"
Range("S1").Value = "Inv Date"
ActiveSheet.UsedRange.Copy PasteToCell
ActiveWorkbook.Close False
End Sub


--
Jim Rech
Excel MVP
"gls858" wrote in message
...
|I recorded macro that imports a text file and adds headers
| to the columns. When I run the macro it opens a new workbook
| and inserts the text file.
|
| Is is possible to add code that will copy the sheet from the
| new workbook into the original or just insert the imported
| file as a new sheet in the original workbook?
|
| Sub LoadBOReport()
| '
| ' Macro1 Macro
| ' Macro recorded 3/15/2005
| '
| ' Keyboard Shortcut: Ctrl+r
| '
| Workbooks.OpenText
| Filename:="C:\DailyBackOrders\BackOrders.txt", Origin:= _
| 437, StartRow:=1, DataType:=xlDelimited,
| TextQualifier:=xlDoubleQuote, _
| ConsecutiveDelimiter:=False, Tab:=False,
| Semicolon:=False, Comma:=False _
| , Space:=False, Other:=True, OtherChar:="|",
| FieldInfo:=Array(Array(1, 2 _
| ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
| 1), Array(6, 1), 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)), _
| TrailingMinusNumbers:=True
| Rows("1:1").Select
| Selection.Insert Shift:=xlDown
| Range("A1").Select
| ActiveCell.FormulaR1C1 = "Item Nbr"
| Range("B1").Select
| ActiveCell.FormulaR1C1 = "CO"
| Range("C1").Select
| ActiveCell.FormulaR1C1 = "Desc"
| Range("D1").Select
| ActiveCell.FormulaR1C1 = "Class"
| Range("E1").Select
| ActiveCell.FormulaR1C1 = "Unit"
| Range("F1").Select
| ActiveCell.FormulaR1C1 = "BO Qty"
| Range("G1").Select
| ActiveCell.FormulaR1C1 = "S Qty"
| Range("H1").Select
| ActiveCell.FormulaR1C1 = "OW"
| Range("I1").Select
| ActiveCell.FormulaR1C1 = "Pick Nbr"
| Range("J1").Select
| ActiveCell.FormulaR1C1 = "Cust Nbr"
| Range("K1").Select
| ActiveCell.FormulaR1C1 = "Dept"
| Range("L1").Select
| ActiveCell.FormulaR1C1 = "Dept Nmae"
| Range("M1").Select
| ActiveCell.FormulaR1C1 = "Entered"
| Range("N1").Select
| ActiveCell.FormulaR1C1 = "Due Date"
| Range("O1").Select
| ActiveCell.FormulaR1C1 = "Cust PO"
| Range("P1").Select
| ActiveCell.FormulaR1C1 = "WMP PO"
| Range("Q1").Select
| ActiveCell.FormulaR1C1 = "Order Date"
| Range("R1").Select
| ActiveCell.FormulaR1C1 = "Due Date"
| Range("S1").Select
| ActiveCell.FormulaR1C1 = "Inv Date"
| End Sub
|
|

Jim Rech wrote:
Assuming you start the macro with the top, left cell selected that you want
to paste to, try this:

snip

Thanks Jim I'll give it a try.

It always amazes me the amount of knowledge that is shared here.
I've learned quite a bit just by lurking. Thanks to all of you
who take the time to share.

gls858

Jim Rech wrote:
Assuming you start the macro with the top, left cell selected that you want
to paste to, try this:


snip

Jim,
Somethings not working nothing happens, but I don't get any error codes
either. If I rem out the six lines you added the import works.

It looks like the line you added are to copy and paste the
imported info into the original workbook. Is that correct?

Here are the exact steps I'm taking

1. open BackOrders.xls

2. run macro

3. this opens a new book with a sheet containing the info. In the title bar
it says BackOrder.txt

4. I select the sheet the import created and move it to BackOrder.xls

It's step four that I'm trying to get into the macro.
Sorry for the confusing naming. I see now I should use different
names for the workbook and the text file.

gls858

If I understood you correctly........this function can be recorded into a
macro as well...........
This will copy Sheet3 of Book7 into Book2, assuming both are open.........

Sub GetSheet()
' Macro1 Macro
' Macro recorded 3/16/2005
Windows("Book7.xls").Activate
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Workbooks("Book2").Sheets(3)
End Sub


Vaya con Dios,
Chuck, CABGx3


"gls858" wrote in message
...
I recorded macro that imports a text file and adds headers
to the columns. When I run the macro it opens a new workbook
and inserts the text file.

Is is possible to add code that will copy the sheet from the
new workbook into the original or just insert the imported
file as a new sheet in the original workbook?

Sub LoadBOReport()
'
' Macro1 Macro
' Macro recorded 3/15/2005
'
' Keyboard Shortcut: Ctrl+r
'
Workbooks.OpenText
Filename:="C:\DailyBackOrders\BackOrders.txt", Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False, Comma:=False _
, Space:=False, Other:=True, OtherChar:="|",
FieldInfo:=Array(Array(1, 2 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), 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)), _
TrailingMinusNumbers:=True
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Item Nbr"
Range("B1").Select
ActiveCell.FormulaR1C1 = "CO"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Desc"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Class"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Unit"
Range("F1").Select
ActiveCell.FormulaR1C1 = "BO Qty"
Range("G1").Select
ActiveCell.FormulaR1C1 = "S Qty"
Range("H1").Select
ActiveCell.FormulaR1C1 = "OW"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Pick Nbr"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Cust Nbr"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Dept"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Dept Nmae"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Entered"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Due Date"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Cust PO"
Range("P1").Select
ActiveCell.FormulaR1C1 = "WMP PO"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Order Date"
Range("R1").Select
ActiveCell.FormulaR1C1 = "Due Date"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Inv Date"
End Sub

CLR wrote:
If I understood you correctly........this function can be recorded into a
macro as well...........
This will copy Sheet3 of Book7 into Book2, assuming both are open.........

Sub GetSheet()
' Macro1 Macro
' Macro recorded 3/16/2005
Windows("Book7.xls").Activate
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Workbooks("Book2").Sheets(3)
End Sub


Vaya con Dios,
Chuck, CABGx3


snip

Thanks CLR. I found that when I tried to record the sheet move
I got a message saying the book would be closed and the changes
would be lost. I only had the one sheet in the book so when I moved it
Excel would try to close the book. I added a sheet and was able to record
the move, but when I added those lines to the macro I got an Out of range
error. I'm sure it's something simple but I'm new at building macros.
Used Excel for a long time just never played with this part. My needs
were basic :-)

I'll give your suggestion a try tomorrow. It's Miller time. I'm outta
here.

gls858

Put our macro in BackOrders.xls and run it after opening this workbook. The
macros handles steps 2, 3 and 4. Also it closes the text file so maybe
that's why you think "nothing is happening". The data should end up in the
cell in Backorders.xls that was selected when you ran the macro.

--
Jim Rech
Excel MVP
"gls858" wrote in message
...
| Jim Rech wrote:
| Assuming you start the macro with the top, left cell selected that you
want
| to paste to, try this:
|
|
| snip
|
| Jim,
| Somethings not working nothing happens, but I don't get any error codes
| either. If I rem out the six lines you added the import works.
|
| It looks like the line you added are to copy and paste the
| imported info into the original workbook. Is that correct?
|
| Here are the exact steps I'm taking
|
| 1. open BackOrders.xls
|
| 2. run macro
|
| 3. this opens a new book with a sheet containing the info. In the title
bar
| it says BackOrder.txt
|
| 4. I select the sheet the import created and move it to BackOrder.xls
|
| It's step four that I'm trying to get into the macro.
| Sorry for the confusing naming. I see now I should use different
| names for the workbook and the text file.
|
| gls858

Jim Rech wrote:
Put our macro in BackOrders.xls and run it after opening this workbook. The
macros handles steps 2, 3 and 4. Also it closes the text file so maybe
that's why you think "nothing is happening". The data should end up in the
cell in Backorders.xls that was selected when you ran the macro.


Jim,
After a some experimentation it seems that that data is not
being pasted to the BackOrders.xls. By commenting out just
this line, ActiveWorkbook.Close False, I see the data has been
imported, but not pasted to BackOrder.xls.

Could the problem be that the imported file is not
actually an .xls file until it's saved?

gls858

CLR wrote:
If I understood you correctly........this function can be recorded into a
macro as well...........
This will copy Sheet3 of Book7 into Book2, assuming both are open.........

Sub GetSheet()
' Macro1 Macro
' Macro recorded 3/16/2005
Windows("Book7.xls").Activate
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Workbooks("Book2").Sheets(3)
End Sub


Vaya con Dios,
Chuck, CABGx3



snip

CLR,
I don't think this will work. The imported file hasn't
been saved so I don't think Excel sees it as a book.


gls858

I only sent that code to show you what your macro might look like if you
recorded it yourself using your own File and Sheet names......the process
does work, and can in fact be created by recording the steps.....this was
just to demonstrate.

Vaya con Dios,
Chuck, CABGx3



"gls858" wrote:

CLR wrote:
If I understood you correctly........this function can be recorded into a
macro as well...........
This will copy Sheet3 of Book7 into Book2, assuming both are open.........

Sub GetSheet()
' Macro1 Macro
' Macro recorded 3/16/2005
Windows("Book7.xls").Activate
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Workbooks("Book2").Sheets(3)
End Sub


Vaya con Dios,
Chuck, CABGx3



snip

CLR,
I don't think this will work. The imported file hasn't
been saved so I don't think Excel sees it as a book.


gls858