help on a macro

		Home \| Contact Us \| FAQ \| Search & Site Map \| Link to Us
		Sign In \| Join \| Other 45 Sites in Network

Discussions Access Excel InfoPath Outlook PowerPoint Publisher Word

Directory User Groups

Related Topics

Outlook Express Internet Explorer Windows MS Server Products More Topics ...

MS Office Forum / Excel / New Users / March 2005

help on a macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gls858 - 16 Mar 2005 19:19 GMT

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

Reply to this Message

Jim Rech - 16 Mar 2005 23:09 GMT

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

Signature

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
[quoted text clipped - 67 lines]
| ActiveCell.FormulaR1C1 = "Inv Date"
| End Sub

Reply to this Message

gls858 - 16 Mar 2005 23:15 GMT

> 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

Reply to this Message

gls858 - 17 Mar 2005 00:24 GMT

> 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

Reply to this Message

Jim Rech - 17 Mar 2005 15:15 GMT

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.

Signature

Jim Rech
Excel MVP

| > Assuming you start the macro with the top, left cell selected that you want
| > to paste to, try this:
[quoted text clipped - 24 lines]
|
| gls858

Reply to this Message

gls858 - 17 Mar 2005 16:55 GMT

> 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

Reply to this Message

Jim Rech - 18 Mar 2005 14:50 GMT

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

Signature

Jim Rech
Excel MVP

| > 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
[quoted text clipped - 11 lines]
|
| gls858

Reply to this Message

gls858 - 18 Mar 2005 16:45 GMT

> 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

Reply to this Message

gls858 - 18 Mar 2005 22:03 GMT

> 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

Reply to this Message

CLR - 18 Mar 2005 23:44 GMT

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

> > I tested the code before I gave it to you so I'm not sure what the problem
> > is. Sorry.
[quoted text clipped - 15 lines]
>
> gls858

Reply to this Message

gls858 - 18 Mar 2005 23:55 GMT

> 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
[quoted text clipped - 27 lines]
>>
>>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 to this Message

CLR - 17 Mar 2005 00:29 GMT

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

> I recorded macro that imports a text file and adds headers
> to the columns. When I run the macro it opens a new workbook
[quoted text clipped - 67 lines]
> ActiveCell.FormulaR1C1 = "Inv Date"
> End Sub

Reply to this Message

gls858 - 17 Mar 2005 00:37 GMT

> If I understood you correctly........this function can be recorded into a
> macro as well...........
[quoted text clipped - 12 lines]
>
>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

Reply to this Message

gls858 - 17 Mar 2005 17:09 GMT

> If I understood you correctly........this function can be recorded into a
> macro as well...........
[quoted text clipped - 10 lines]
> 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

Reply to this Message

CLR - 17 Mar 2005 20:13 GMT

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

> > If I understood you correctly........this function can be recorded into a
> > macro as well...........
[quoted text clipped - 18 lines]
>
> gls858

Reply to this Message

gls858 - 17 Mar 2005 20:38 GMT

> 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
[quoted text clipped - 26 lines]
>>
>>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

Reply to this Message

Sign In

Join

My Latest Posts

My Monitored Threads

My Photo Gallery

My Homepage

Start New Thread

Enable EMail Alerts

Rate this Thread

©2008 Advenet LLC Privacy Policy - Terms of Use

This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.