Open Copy Close
 

I have these two Macro's at present: -

Sub ListFiles()
F = Dir("C:\*.XLS")
Do While Len(F) 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

Sub CopyInfo()
Workbooks.Open Filename:=Range("A1").Value
Range("A:B").Copy Destination:=Workbooks("Book1").Range("B:C")
Workbooks.Close Filename:=Range("A1").Value
MsgBox ("Completed Copying")

End Sub

As you can see I want the Macro to open the file name in A1 then copy
the information, then paste it into Book1.xls, then cose the Filename
in A1.

So far it opens the workbook, but won't close it. I dont think I can
use the close command here, any ideas?


---
Message posted from http://www.ExcelForum.com/

Open Copy Close
 

Hi
Try this

Sub CopyInfo()
Dim wbOne as Workbook
Set wbOne = Workbooks.Open Filename:=Range("A1").Value
Range("A:B").Copy Destination:=Workbooks("Book1").Range("B:C")
wbOne.Close
MsgBox ("Completed Copying")
End Sub

Also ActiveWorkbook.close should work


ianripping wrote in message
...
I have these two Macro's at present: -

Sub ListFiles()
F = Dir("C:\*.XLS")
Do While Len(F) 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

Sub CopyInfo()
Workbooks.Open Filename:=Range("A1").Value
Range("A:B").Copy Destination:=Workbooks("Book1").Range("B:C")
Workbooks.Close Filename:=Range("A1").Value
MsgBox ("Completed Copying")

End Sub

As you can see I want the Macro to open the file name in A1 then copy
the information, then paste it into Book1.xls, then cose the Filename
in A1.

So far it opens the workbook, but won't close it. I dont think I can
use the close command here, any ideas?


---
Message posted from http://www.ExcelForum.com/

Open Copy Close
 

Set wbOne = Workbooks.Open Filename:=Range("A1").Value forms a synta
error

--
Message posted from http://www.ExcelForum.com

Open Copy Close
 

Sorry, stupid mistake on my part!!!
Set wbOne = Workbooks.Open(Filename:=Range("A1").Value)
should sort it
;-)


ianripping wrote in message
...
Set wbOne = Workbooks.Open Filename:=Range("A1").Value forms a syntax
error.


---
Message posted from http://www.ExcelForum.com/

Open Copy Close
 

OK I've got it its like this

Workbooks.Open Filename:=Range("A1").Value
Range("A:A").Select
Selection.Copy
ActiveWorkbook.Close SaveChanges:=False
Windows("Book1.xls").Activate
Range("C:C").Select
ActiveSheet.Paste
MsgBox ("Completed Copying")

BUT! Excel asks do you want to copy this large amount of info blah
blah

Then do you want to paste this info even though the target cell isn't
as big blah blah


Is there a way to turn off these prompts?


---
Message posted from http://www.ExcelForum.com/

Open Copy Close
 

I haven't been able to recreate your problem but you could try inserting
this line before the copy command
Application.DisplayAlerts = False
Set back to True at the end of the routine
;-)

ianripping wrote in message
...
OK I've got it its like this

Workbooks.Open Filename:=Range("A1").Value
Range("A:A").Select
Selection.Copy
ActiveWorkbook.Close SaveChanges:=False
Windows("Book1.xls").Activate
Range("C:C").Select
ActiveSheet.Paste
MsgBox ("Completed Copying")

BUT! Excel asks do you want to copy this large amount of info blah
blah

Then do you want to paste this info even though the target cell isn't
as big blah blah


Is there a way to turn off these prompts?


---
Message posted from http://www.ExcelForum.com/