I'm attempting to write a macro that goes to a workbook
that's not open, copy data from it, and paste it into the
workbook that is open. Is this possible? If so, how do
I "get" to the workbook that's closed to copy data from it?

Any help would be appreciated.

Thanks,

Kevin G

p.s. - has anyone noticed any of their posts not
displaying? I posted one yesterday that never showed up???

Hi Kevin

See this Webpage from John Walkenbach
http://j-walk.com/ss/excel/tips/tip82.htm

But opening the workbook is in most cases faster with
ScreenUpdating to false

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Kevin G" wrote in message ...
I'm attempting to write a macro that goes to a workbook
that's not open, copy data from it, and paste it into the
workbook that is open. Is this possible? If so, how do
I "get" to the workbook that's closed to copy data from it?

Any help would be appreciated.

Thanks,

Kevin G

p.s. - has anyone noticed any of their posts not
displaying? I posted one yesterday that never showed up???

You can do it with a range also but this is faster
This will open the file test.xls and copy the range in the Activeworkbook
I use in both "Sheet1" as the Sheet name

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\test.xls")
Wb2.Sheets("Sheet1").Range("a2:h600").Copy _
Wb1.Sheets("sheet1").Range("a1")
Wb2.Close False
Application.ScreenUpdating = True
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Kevin G" wrote in message ...
If I'm reading this right this will only get the value
from one cell not a range. I'm trying to copy a range
(a2:h600) and then paste it into an active workbook.

If this isn't possible, I'll stop trying and just open the
workbook to get it to work.

KG
-----Original Message-----
Hi Kevin

See this Webpage from John Walkenbach
http://j-walk.com/ss/excel/tips/tip82.htm

But opening the workbook is in most cases faster with
ScreenUpdating to false

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Kevin G" wrote in message
...
I'm attempting to write a macro that goes to a workbook
that's not open, copy data from it, and paste it into
the
workbook that is open. Is this possible? If so, how do
I "get" to the workbook that's closed to copy data from
it?

Any help would be appreciated.

Thanks,

Kevin G

p.s. - has anyone noticed any of their posts not
displaying? I posted one yesterday that never showed
up???


.

Steve

Thanks for the code.

It appeared to almost work. My guess is that the 'import table wizard'
needs somehow to be invoked, so nothing ends up happening, cause of
the 'non- .xls file type I guess? So I was not able to try your
LASTROW function(?).

Where the earlier code of Rons' opened an excel workbook and pasted in
data; would there be a way to marry up your code with his to select
the required file from a particular folder, the copy&paste in the
data, in the case of excel files.

Incidentally, with his code;" Range("a2:h600").Copy _ ", only 99
rows were copied in?

Many Thanks
Jon





"steve" wrote in message ...
Jon,

#1. Can the name of the file to be opened be typed into a window on
initiating the macro above. (I would like to be able to run this macro
each week over a new update file).
Better yet, you can use this code to initiate the Open file dialog and pick
the file you want...

Sub OpenMyFile()
' code "borrowed" from the ng
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
'' List Files in Immediate Window
Debug.Print GetFiles(iFiles)
Next
End If
End Sub

You may have to play with
(FileFilter:="Text Files (*.txt),*.txt", _
(I am not up on ascii type files)
You may not even need that part.

#2. Can the macro open an ascii file instead of an .xls file ? If so,
the formatting would be consistent each time it is run. This is
delimited, comma separated, with the second column being 'date - MDY'.
The extension is .uut

Once open you may automatically be put into the text wizard. Or you may
have
to use Text to Columns in the Data menu. You can record this part and make
it
part of your code.

Also, could the whole table, in the ascii be copied? The number of
records vary for each file, but the fields are the same.

Than you can use

Dim LASTROW As Long

LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row

to determine the number of records. Change "A" to the column that
will always have the greatest number of records (if not all columns
have data to the bottom of the file).

Than
Range(Cells(1, 1),Cells(LASTROW, 12)).Copy

to copy. (this copies columns A to L from row 1 to Lastrow)

steve

"Jon Macmichael" wrote in message
om...
"Ron de Bruin" wrote in message
...
You can do it with a range also but this is faster
This will open the file test.xls and copy the range in the
Activeworkbook
I use in both "Sheet1" as the Sheet name

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\test.xls")
Wb2.Sheets("Sheet1").Range("a2:h600").Copy _
Wb1.Sheets("sheet1").Range("a1")
Wb2.Close False
Application.ScreenUpdating = True
End Sub

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


<<<<<

Are two further steps possible here;

#1. Can the name of the file to be opened be typed into a window on
initiating the macro above. (I would like to be able to run this macro
each week over a new update file).

#2. Can the macro open an ascii file instead of an .xls file ? If so,
the formatting would be consistent each time it is run. This is
delimited, comma separated, with the second column being 'date - MDY'.
The extension is .uut
Also, could the whole table, in the ascii be copied? The number of
records vary for each file, but the fields are the same.

Thanks
Jon Macmichael

Jon,

from Ron's code add the following
Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim LASTROW as Long
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\test.xls")
LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row

Wb2.Sheets("Sheet1").Range(Cells(2,1),Cells(LASTRO W,8)).Copy _
Wb1.Sheets("sheet1").Range("a1")
Wb2.Close False
Application.ScreenUpdating = True
End Sub

Should work. Post back if you still have problems.

And you're Welcome!

steve

"Jon Macmichael" wrote in message
om...
Steve

Thanks for the code.

It appeared to almost work. My guess is that the 'import table wizard'
needs somehow to be invoked, so nothing ends up happening, cause of
the 'non- .xls file type I guess? So I was not able to try your
LASTROW function(?).

Where the earlier code of Rons' opened an excel workbook and pasted in
data; would there be a way to marry up your code with his to select
the required file from a particular folder, the copy&paste in the
data, in the case of excel files.

Incidentally, with his code;" Range("a2:h600").Copy _ ", only 99
rows were copied in?

Many Thanks
Jon





"steve" wrote in message
...
Jon,

#1. Can the name of the file to be opened be typed into a window on
initiating the macro above. (I would like to be able to run this macro
each week over a new update file).
Better yet, you can use this code to initiate the Open file dialog and
pick
the file you want...

Sub OpenMyFile()
' code "borrowed" from the ng
Dim GetFiles As Variant
Dim iFiles As Long
Dim nFiles As Long
GetFiles = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select Files To Open", MultiSelect:=True)
If TypeName(GetFiles) = "Boolean" Then
''' GetFiles is False if GetOpenFileName is Canceled
MsgBox "No Files Selected", vbOKOnly, "Nothing Selected"
End
Else
''' GetFiles is Array of Strings (File Names)
''' File names include Path
nFiles = UBound(GetFiles)
For iFiles = 1 To nFiles
'' List Files in Immediate Window
Debug.Print GetFiles(iFiles)
Next
End If
End Sub

You may have to play with
(FileFilter:="Text Files (*.txt),*.txt", _
(I am not up on ascii type files)
You may not even need that part.

#2. Can the macro open an ascii file instead of an .xls file ? If so,
the formatting would be consistent each time it is run. This is
delimited, comma separated, with the second column being 'date - MDY'.
The extension is .uut

Once open you may automatically be put into the text wizard. Or you may
have
to use Text to Columns in the Data menu. You can record this part and
make
it
part of your code.

Also, could the whole table, in the ascii be copied? The number of
records vary for each file, but the fields are the same.

Than you can use

Dim LASTROW As Long

LASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row

to determine the number of records. Change "A" to the column that
will always have the greatest number of records (if not all columns
have data to the bottom of the file).

Than
Range(Cells(1, 1),Cells(LASTROW, 12)).Copy

to copy. (this copies columns A to L from row 1 to Lastrow)

steve

"Jon Macmichael" wrote in message
om...
"Ron de Bruin" wrote in message
...
You can do it with a range also but this is faster
This will open the file test.xls and copy the range in the
Activeworkbook
I use in both "Sheet1" as the Sheet name

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\test.xls")
Wb2.Sheets("Sheet1").Range("a2:h600").Copy _
Wb1.Sheets("sheet1").Range("a1")
Wb2.Close False
Application.ScreenUpdating = True
End Sub

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


<<<<<

Are two further steps possible here;

#1. Can the name of the file to be opened be typed into a window on
initiating the macro above. (I would like to be able to run this macro
each week over a new update file).

#2. Can the macro open an ascii file instead of an .xls file ? If so,
the formatting would be consistent each time it is run. This is
delimited, comma separated, with the second column being 'date - MDY'.
The extension is .uut
Also, could the whole table, in the ascii be copied? The number of
records vary for each file, but the fields are the same.

Thanks
Jon Macmichael