PDA

View Full Version : Copy from closed workbook



Dibbley247
12-19-2012, 10:24 AM
Hi

I'm trying to copy a selected range (1 cell) from a closed workbook, and paste it into a designated Sheet and range on the current sheet.

I came across the following code on this site, and have manage to change it so it copies what I want. But when it pastes, it always pastes into A1.
I am in the infancy of learning VBA.

I changed it so it copies from E5, but there could be a better way than this?????
Also when this goes into my actual workbook after testing, should I put this code into the current worksheet rather than as a module, as i'm guessing if it was a module, then i'll have to state what sheet this will have to paste in?
I would like to paste into...lets say..Sheets."Summary" Range."F10"
Thanks.



Option Explicit


Sub GetDataDemo()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Testfile.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 1
Const NumColumns& = 1
FilePath = ActiveWorkbook.Path & "\"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("E5").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)

End Function

p45cal
12-19-2012, 01:12 PM
Leave the function as it wasPrivate Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

You only need a short bit of code to put the data in the cell you want, eg. to put it into cell B2 you want:Range("B2") = GetData("C:\Documents and Settings\Dibbley\My Documents\", "book1.xls", "Sheet1", "E5")
Obviously the path and filename and sheet name all have to be adjusted to suit you. The above has been tested here and works.

Dibbley247
12-19-2012, 01:54 PM
I see what this does, but where do I put this? Does this replace the whole line that starts with RANGE(ADDRESS).....

Sorry for simple questions, I only started using macros about 2 months ago.

Thanks.

p45cal
12-19-2012, 02:11 PM
I see what this does, but where do I put this? Does this replace the whole line that starts with RANGE(ADDRESS).....

Sorry for simple questions, I only started using macros about 2 months ago.

Thanks.Well, at its simplest, put it into a sub in the same code-module as you have the put the Function.
Sub test()
Range("B2") = GetData("C:\Documents and Settings\Dibbley\My Documents\", "book1.xls", "Sheet1", "E5")
End subthen run the macro test.

Dibbley247
12-19-2012, 02:28 PM
Ive put it in where I think it goes, and it pastes the total into where I asked.
but I have the whole list of sums down column A also which I don't want.

Option Explicit


Sub GetDataDemo()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub

Sub test()
Range("B2") = GetData("C:\Users\Duane\Documents\Rewrap folder\test folder\GetData\", "book1.xls", "Sheet1", "E5")
End Sub
Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function




I have however found that where it has:

For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row

If I insert the amount of rows along and columns down like ROW + 9 COLUMN + 5 this will work.

p45cal
12-19-2012, 03:06 PM
Again, you don't need the GetDataDemo code AT ALL. It is just that, a demo.
You only need the Function GetData code and a one-liner sub such as test above, in the same standard code-module.
You will need to adjust the 4 arguments (the 4 strings) to suit you, so: the file path must exist, the filename must exist, the sheet name must exist, and it's useful if there's something in the cell you want to grab data from. All these 4 arguments relate to the file you want to get data from.

To answer your question in the first message in this thread, the destination sheet and cell for this data can be specified by:
Sheets("Summary").Range("F10") = GetData("C:\Documents and Settings\Dibbley\My Documents\", "book1.xls", "Sheet1", "E5")
To summarise, all you need in a standard code-module is the following 8 lines, adjusted with strings that suit your file paths, file names, sheet names etc.:Sub test()
Sheets("Summary").Range("F10") = GetData("C:\Documents and Settings\Dibbley\My Documents\", "book1.xls", "Sheet1", "E5")
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function
and nothing else.
Run the test macro.

Dibbley247
12-20-2012, 08:58 AM
Ah I get you now sorry, I'll try it later when on the PC. I need to find a decent book for beginners really to UNDERSTAND the basics rather than trying to figure out and getting it wrong most of the time! Thanks for your help, I'll let you know how I get on!!!

Dibbley247
12-20-2012, 12:13 PM
Brilliant!!!!! I can use this across multiple things now!

Thanks for your help and time!!

p45cal
12-20-2012, 01:46 PM
You realise that putting the following formula in cell F10 of the Summary sheet:
='C:\Documents and Settings\Dibbley\My Documents\[book1.xls]Sheet1'!E5
will get the value in cell E5 of Sheet1 in the closed file called book1.xls in the path C:\Documents and Settings\Dibbley\My Documents\

You can later convert this formula into its value by copy / paste-special|values in situ, either manually or with VBA. So do you need the GetData at all?

Dibbley247
12-20-2012, 01:48 PM
I changed it to open a file but with the sheet name in A1 and this also worked, but when I changed the values to B1 the pasted value is 0.

Is there something that I have missed on this when changing it to B1?

Sub test()
Sheets("Summary").Range("F10") = GetData("C:\Users\Duane\Documents\Rewrap folder\Testfolder2\", Sheets("Summary").Range("B1").Value, "Sheet1", "E5")
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Address).Range("B1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

Dibbley247
12-20-2012, 01:51 PM
Yes I do need this macro, as it's being changed to open files relevant to cell values, as this will change daily, otherwise I would have gone for the formula you mentioned.

p45cal
12-20-2012, 02:08 PM
Your line:
Sheets("Summary").Range("F10") = GetData("C:\Users\Duane\Documents\Rewrap folder\Testfolder2\", Sheets("Summary").Range("B1").Value, "Sheet1", "E5")

requires that B1 in the Summary sheet is a correct file name, including the extension, such as Book1.xls, book1.xlsx, book1.xlsm etc. etc., no leading/trailing spaces, and the name exactly right.
Secondly, the cell E5 in sheet Sheet1, if it's empty will return 0.

Dibbley247
12-20-2012, 02:18 PM
I'll take another look, but I'm sure that everything is spelt correctly, as it all worked when using A1.

Would this part have anything to do with it????

Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)

The part in red.... is this looking at columns and rows? As I did try to change one and both of them to 2 when using B1 thinking it was counting of some sort?

Kenneth Hobs
12-20-2012, 02:36 PM
Do not change anything in the Function. Change the input parameter values only in your Sub.

From the Immediate Windows if you type this below and press Enter key, what is returned?
? Sheets("Summary").Range("B1").Value
This is the value from your activework's Summary sheet's range B1. It must be book1.xlsx or such as explained previously.

Here is what I use.
Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function

Dibbley247
12-20-2012, 02:50 PM
Ah now that worked!

So what does this me\n highlighted in RED then????

Sub test()
Sheets("Summary").Range("F10") = GetData("C:\Users\Duane\Documents\Rewrap folder\Testfolder2\", Sheets("Summary").Range("B1").Value, "Sheet1", "E5")
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function


I am planning on using this muliple times on the same sheet to open several books one after another, and paste them into relevant cells. So I will be using say A1 B1 C1 D1 as part file names, could this cause a problem for me if repeating this several times?

Thanks

Kenneth Hobs
12-20-2012, 03:05 PM
Notice that I don't use the word Address as an input parameter name since Address is a property of the Range object.

As I said earlier, send your data as values for the input parameter. What that line of code does in the routine is that it converts the A1 format to R1C1 format as that is required for that old excel function. You can test those sorts of things in the Immediate Window.

e.g. Type this in Immediate window and press Enter key to see the R1C1 address for B2 which is R1C2.
?Range("B2").Range("A1")Address(, , xlR1C1)
Obviously, we could write the function to accept R1C1 notation for the input parameter but many like the column letter row number notation A1 type of format.

Dibbley247
12-20-2012, 03:56 PM
This is getting a bit over my head! I seriously need to buy a book to learn from the basics. I don't even know what the simple things mean like DIM, INTEGER etc, I've been typing in what I want and getting help and changing what I think I understand and so on. I'm getting confused a bit now it's getting late!

I did earlier change everything to B2 and even changed to R1C2 but still got 0.

When I only changed the top line to B2 and left everything else it worked.
Better get to bed as I have to get up for work in a few hours. All of your help is much appreciated, and I will return to this in a couple of days, when I have some more hours spare! And will let you guys know how I'm getting on with this.

Is there any type of books or online help you guys would recommend. Even if it requires a couple of books, one to start me off with understanding then a more advanced one, it's as if I need something to give me a list of what Syntax are used in Macro and what they do, bit like in excel the list of formulas and an explanation / example of what that formula does.

Thanks again!

Kenneth Hobs
12-20-2012, 04:54 PM
There are hundreds if not thousands of books about Excel. Just look at amazon.com. For the beginner to learn VBA for Excel, I like: http://spreadsheetpage.com/

The Visual Basic Editor, VBE, lets you press F1 in or near a command word and you will get the same help as if you had searched for it in the browser with F2.

Some other helpful tutorials:
http://www.excel-vba.com/excel-vba-solutions-beginners.htm
http://www.tushar-mehta.com/excel/vba/
http://www.datapigtechnologies.com/ExcelMain.htm
other links in: http://www.mrexcel.com/forum/lounge-v-2-0/352116-excel-help.html

Why would you pass R1C2? The function was designed to accept string values though it is type variant as posted here with the assumption that you will pass A1 style string for the range.