Copying Data from closed workbooks

  • Thread starter Thread starter Kevin G
  • Start date Start date
K

Kevin G

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
 
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
 
Jon,

from Ron's code add the followingLASTROW = Cells(Rows.COUNT, "A").End(xlUp).Row

Wb2.Sheets("Sheet1").Range(Cells(2,1),Cells(LASTROW,8)).Copy _
Should work. Post back if you still have problems.

And you're Welcome!

steve

Jon Macmichael said:
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






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.


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.


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
Click to expand...
[/QUOTE]
 
Back
Top