Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 | | |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
CLR wrote:
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 Understood. Thanks for the help. Any idea if Excel sees the imported file as a workbook even though it hasn't benn saved? gls858 |
#12
|
|||
|
|||
I tested the code before I gave it to you so I'm not sure what the problem
is. Sorry. -- Jim Rech Excel MVP "gls858" wrote in message ... | 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 |
#13
|
|||
|
|||
Jim Rech wrote:
I tested the code before I gave it to you so I'm not sure what the problem is. Sorry. 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 |
#14
|
|||
|
|||
Jim Rech wrote:
I tested the code before I gave it to you so I'm not sure what the problem is. Sorry. 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 |
#15
|
|||
|
|||
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 "gls858" wrote in message ... Jim Rech wrote: I tested the code before I gave it to you so I'm not sure what the problem is. Sorry. 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 |
#16
|
|||
|
|||
CLR wrote:
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 "gls858" wrote in message ... Jim Rech wrote: I tested the code before I gave it to you so I'm not sure what the problem is. Sorry. 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Can't get simple macro to run | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Macro and If Statement | Excel Discussion (Misc queries) |