Need macro to allow me to go out into another excel file and copy and bring data back..

Hi,

I'm looking to have a macro that I've started (below) to allow me to go
out to a file that is always in the same location with the same name
and copy information from it and bring it back into the file I was
originally in.  The file from which I want to get the information from
is updated automatically every 24 hours from data dumped into it from
our MRP system.  The macro is to be stored in my "personal.xls" file.
The problem is that the original file name is always different.  So how
can I make the macro come back to this file that has a different name
each time to paste the updated information into it?

In the macro below, it is the "190-DA125-F1V1-BOM-01.xls" file that has
a different name each time.  Somehow, I need to run a macro that will
know to come back to the original file I have open each time and paste
the information in without having to worry about its name....


please help,

Steve



Sub Macro5()
'
' Macro5 Macro
' Macro recorded 2/6/2006 by User
'

'
    Workbooks.Open Filename:="S:\Doc\BOM Tools\Alt_Items_List.xls"
    Range("A:A,J:J").Select
    Range("J1").Activate
    Selection.Copy
    Windows("190-DA125-F1V1-BOM-01.xls").Activate
    Range("L1").Select
    ActiveSheet.Paste
End Sub

0
sspatriots (78)
2/6/2006 11:28:42 PM
excel 39879 articles. 2 followers. Follow

10 Replies
660 Views

Similar Articles

[PageSpeed] 37

Try using:

Sub Macro()
    dim wkbCurrent as Workbook
    set wkbCurrent = ActiveWorkbook

    Workbooks.Open Filename:="S:\Doc\BOM Tools\Alt_Items_List.xls"
    Range("A:A,J:J").Select
    Range("J1").Activate
    Selection.Copy

    wkbCurrent.Activate

    Range("L1").Select
    ActiveSheet.Paste
End Sub


"set wkbCurrent = ActiveWorkbook" sets the active book to wkbCurrent,
and then
"wkbCurrent.Activate" goes back to that (the original workbook).

-  Kurtis Story

0
2/6/2006 11:49:28 PM
Sub filename()
 originalfile = ActiveWorkbook.Name
End Sub

But you may like this idea better where you need not know the name of the 
workbook you are in.

Sub GetInfofromclosedworkbook()
  Workbooks.Open "c:\yourfolder\yoursourcefile.xls"
  Worksheets("thesourcesheet").Range("a:a,j:j").Copy _
    ThisWorkbook.Worksheets("sheet1").Range("a1")
  ActiveWorkbook.Close SaveChanges:=False
End Sub

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Steve" <sspatriots@yahoo.com> wrote in message 
news:1139268522.693334.279630@g44g2000cwa.googlegroups.com...
> Hi,
>
> I'm looking to have a macro that I've started (below) to allow me to go
> out to a file that is always in the same location with the same name
> and copy information from it and bring it back into the file I was
> originally in.  The file from which I want to get the information from
> is updated automatically every 24 hours from data dumped into it from
> our MRP system.  The macro is to be stored in my "personal.xls" file.
> The problem is that the original file name is always different.  So how
> can I make the macro come back to this file that has a different name
> each time to paste the updated information into it?
>
> In the macro below, it is the "190-DA125-F1V1-BOM-01.xls" file that has
> a different name each time.  Somehow, I need to run a macro that will
> know to come back to the original file I have open each time and paste
> the information in without having to worry about its name....
>
>
> please help,
>
> Steve
>
>
>
> Sub Macro5()
> '
> ' Macro5 Macro
> ' Macro recorded 2/6/2006 by User
> '
>
> '
>    Workbooks.Open Filename:="S:\Doc\BOM Tools\Alt_Items_List.xls"
>    Range("A:A,J:J").Select
>    Range("J1").Activate
>    Selection.Copy
>    Windows("190-DA125-F1V1-BOM-01.xls").Activate
>    Range("L1").Select
>    ActiveSheet.Paste
> End Sub
> 


0
dguillett1 (2487)
2/7/2006 12:18:26 AM
Hi,

Thanks, I tried the later of the two suggestions first, however I keep
getting this Display Alert that says "There is a large amount of
information on the Clipboard.  Dou you want to be able to paste this
information into another program later?  How can I make this macro
answer yes to that question so that it doesn't get held up with the
alert and without having to change any of my current Excel settings?


Please advise,

Steve

0
sspatriots (78)
2/7/2006 2:24:54 PM
try adding
application.cutcopymode=false

Sub GetInfofromclosedworkbook()
  Workbooks.Open "c:\yourfolder\yoursourcefile.xls"
  Worksheets("thesourcesheet").Range("a:a,j:j").Copy _
    ThisWorkbook.Worksheets("sheet1").Range("a1")


application.cutcopymode=false

  ActiveWorkbook.Close SaveChanges:=False
End Sub

-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Steve" <sspatriots@yahoo.com> wrote in message 
news:1139322294.391484.157560@o13g2000cwo.googlegroups.com...
> Hi,
>
> Thanks, I tried the later of the two suggestions first, however I keep
> getting this Display Alert that says "There is a large amount of
> information on the Clipboard.  Dou you want to be able to paste this
> information into another program later?  How can I make this macro
> answer yes to that question so that it doesn't get held up with the
> alert and without having to change any of my current Excel settings?
>
>
> Please advise,
>
> Steve
> 


0
dguillett1 (2487)
2/7/2006 2:47:04 PM
That took care of it.  I do have one more question though.  Is there
something that I could add into the macro that will make the source
file (the one that I'm copying the information from) close up after
I've copied what I need, rather than have it remain open?


Thanks, 

Steve

0
sspatriots (78)
2/7/2006 4:58:45 PM
I tried your suggestion as well and it worked great too.  If you have
any suggestions as to my last request to Don, feel free to comment.
Lord know I need all the help I can get when it comes to Excel
macros...


Thanks,

Steve

0
sspatriots (78)
2/7/2006 5:00:41 PM
On MY test of the macro, as written, it DID close
  ActiveWorkbook.Close SaveChanges:=False
-- 
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Steve" <sspatriots@yahoo.com> wrote in message 
news:1139331525.137018.28990@f14g2000cwb.googlegroups.com...
> That took care of it.  I do have one more question though.  Is there
> something that I could add into the macro that will make the source
> file (the one that I'm copying the information from) close up after
> I've copied what I need, rather than have it remain open?
>
>
> Thanks,
>
> Steve
> 


0
dguillett1 (2487)
2/7/2006 10:41:17 PM
Thanks, somehow I did manage to make it work.  One of the things that
was giving me problems was where I added in the
"application.cutcopymode=false" bit.  It made me keep losing the
information that was on the clipboard for which I was trying to bring
back into my original workbook......


Regards,  

Steve

0
sspatriots (78)
2/8/2006 3:29:27 PM
Thanks, somehow I did manage to make it work.  One of the things that
was giving me problems was where I added in the
"application.cutcopymode=false" bit.  It made me keep losing the
information that was on the clipboard for which I was trying to bring
back into my original workbook......


Regards,  

Steve

0
sspatriots (78)
2/8/2006 3:29:30 PM
Don,
Sorry to but in on somebody elses question but I am trying to do a similar 
thing but I need to copy the values only not the formulaes behind would there 
be a way to alter your coding to do this.
(by the way tried your method and it worked spot on)

"Don Guillett" wrote:

> Sub filename()
>  originalfile = ActiveWorkbook.Name
> End Sub
> 
> But you may like this idea better where you need not know the name of the 
> workbook you are in.
> 
> Sub GetInfofromclosedworkbook()
>   Workbooks.Open "c:\yourfolder\yoursourcefile.xls"
>   Worksheets("thesourcesheet").Range("a:a,j:j").Copy _
>     ThisWorkbook.Worksheets("sheet1").Range("a1")
>   ActiveWorkbook.Close SaveChanges:=False
> End Sub
> 
> -- 
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "Steve" <sspatriots@yahoo.com> wrote in message 
> news:1139268522.693334.279630@g44g2000cwa.googlegroups.com...
> > Hi,
> >
> > I'm looking to have a macro that I've started (below) to allow me to go
> > out to a file that is always in the same location with the same name
> > and copy information from it and bring it back into the file I was
> > originally in.  The file from which I want to get the information from
> > is updated automatically every 24 hours from data dumped into it from
> > our MRP system.  The macro is to be stored in my "personal.xls" file.
> > The problem is that the original file name is always different.  So how
> > can I make the macro come back to this file that has a different name
> > each time to paste the updated information into it?
> >
> > In the macro below, it is the "190-DA125-F1V1-BOM-01.xls" file that has
> > a different name each time.  Somehow, I need to run a macro that will
> > know to come back to the original file I have open each time and paste
> > the information in without having to worry about its name....
> >
> >
> > please help,
> >
> > Steve
> >
> >
> >
> > Sub Macro5()
> > '
> > ' Macro5 Macro
> > ' Macro recorded 2/6/2006 by User
> > '
> >
> > '
> >    Workbooks.Open Filename:="S:\Doc\BOM Tools\Alt_Items_List.xls"
> >    Range("A:A,J:J").Select
> >    Range("J1").Activate
> >    Selection.Copy
> >    Windows("190-DA125-F1V1-BOM-01.xls").Activate
> >    Range("L1").Select
> >    ActiveSheet.Paste
> > End Sub
> > 
> 
> 
> 
0
Popeye (6)
4/20/2006 5:12:01 PM
Reply:

Similar Artilces: