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

[Steve]

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

 

[Temporalis]

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

 

[Don Guillett]

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

 

[Steve]

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

 

[Don Guillett]

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

 

[Steve]

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

 

[Steve]

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

 

[Don Guillett]

On MY test of the macro, as written, it DID close
ActiveWorkbook.Close SaveChanges:=False

 

[Steve]

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

 

[Steve]

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

 

[Guest]

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)