help on a macro

G

gls858

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
 
J

Jim Rech

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
|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
|
|
 
G

gls858

Jim said:
Assuming you start the macro with the top, left cell selected that you want
to paste to, try this:
Click to expand...
snip<
Click to expand...

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
 
G

gls858

Jim said:
Assuming you start the macro with the top, left cell selected that you want
to paste to, try this:
Click to expand...
snip<
Click to expand...

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
 
C

CLR

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
 
G

gls858

CLR said:
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<
Click to expand...

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
 
J

Jim Rech

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
| 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
 
G

gls858

Jim said:
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.
Click to expand...

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
 
G

gls858

CLR said:
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
Click to expand...
snip<
Click to expand...

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
 
C

CLR

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
 
G

gls858

CLR said:
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



:
Click to expand...
Understood. Thanks for the help. Any idea if Excel sees the
imported file as a workbook even though it hasn't benn saved?


gls858
 
J

Jim Rech

I tested the code before I gave it to you so I'm not sure what the problem
is. Sorry.

--
Jim Rech
Excel MVP
| 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
 
G

gls858

Jim said:
I tested the code before I gave it to you so I'm not sure what the problem
is. Sorry.
Click to expand...
OK thanks for the help Jim. I'm probably not
explaining it correctly. I'll work with it some
more but for now the person that wants the report
says it's not a problem to just move the sheet.

gls858
 
G

gls858

Jim said:
I tested the code before I gave it to you so I'm not sure what the problem
is. Sorry.
Click to expand...
Jim & CLR

Not that it really matters but I finally got it to work.
I added the lines below. Now the macro pastes the info into the workbook
and closes the work book created by the import.


Cells.Select
Selection.Copy
Windows("BackOrders.xls").Activate
ActiveSheet.Paste
Application.SendKeys ("Tab,Return")
Workbooks("BackOrders.txt").Close SaveChanges:=False

Thanks for the help

gls858
 
C

CLR

Of course it really matters! It's important that this place is here so OP's
can get the answers they need, or better yet gain enough info that they can
figure them out themselves. And it's especially good when the issues are
posted to a successful conclusion like you've just done............that way
everybody gains. Thanks for the feedback......and keep coming back.

Vaya con Dios,
Chuck, CABGx3
 
G

gls858

CLR said:
Of course it really matters! It's important that this place is here so OP's
can get the answers they need, or better yet gain enough info that they can
figure them out themselves. And it's especially good when the issues are
posted to a successful conclusion like you've just done............that way
everybody gains. Thanks for the feedback......and keep coming back.

Vaya con Dios,
Chuck, CABGx3
Click to expand...
I know I always like feed back. I'm sure what I have probably doesn't
follow the conventions, but hey it worked. Thats a start :)

I have another question but I'll start a new thread. Time now to
head for home and fire up the tube and watch some basketball.


gls858
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem with macro 0
Macro 3
Macro moving with daily date 2
Open a GroupWise .NAB File 6
Writing Data with a macro??? 7
Complie Syntax Error 6
Excel application.quit in macro problem 3
Macro Flexability 7

Top