Extra data (specific cells) from various close workbook

mullas007 · Sep 22, 2010

I am pretty new with VBA - am actually an accountant. But help is needed.

As the subject says, I need data from specific cells of various cloded workbooks and the data have to show a a summary sheet at various specific cells. ie. data from A2 (closed workbook 1) must show in C3 (in another workbook sheet 1), and similarly for other data extracts.

I have located this code from a website:

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Crlee%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5Crlee%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5Crlee%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> Sub GetDataDemo()<o

></o

>
<o

> </o

>
Dim FilePath$, Row&, Column&, Address$<o

></o

>
<o

></o

>
'change constants & FilePath below to suit<o

></o

>
'***************************************<o

></o

>
Const FileName$ = "Book1.xls"<o

></o

>
Const SheetName$ = "Sheet1"<o

></o

>
Const NumRows& = 10<o

></o

>
Const NumColumns& = 10<o

></o

>
FilePath = ActiveWorkbook.Path & "\"<o

></o

>
'***************************************<o

></o

>
<o

></o

>
DoEvents<o

></o

>
Application.ScreenUpdating = False<o

></o

>
If Dir(FilePath & FileName) = Empty Then<o

></o

>
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"<o

></o

>
Exit Sub<o

></o

>
End If<o

></o

>
For Row = 1 To NumRows<o

></o

>
For Column = 1 To NumColumns<o

></o

>
Address = Cells(Row, Column).Address<o

></o

>
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)<o

></o

>
Columns.AutoFit<o

></o

>
Next Column<o

></o

>
Next Row<o

></o

>
ActiveWindow.DisplayZeros = False<o

></o

>
End Sub<o

></o

>
<o

> </o

>
Private Function GetData(Path, File, Sheet, Address)<o

></o

>
Dim Data$<o

></o

>
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _<o

></o

>
Range(Address).Range("A1").Address(, , xlR1C1)<o

></o

>
GetData = ExecuteExcel4Macro(Data)<o

></o

>
<o

> </o

>
End Function

Its work great but I just don't know programming to alter the Private Functions part to extract and place the specific data.

Please understand I have no programming background but understand the logics of things.

Looking forward to your replies.
<o

></o

>

jasmith4 · Sep 12, 2012

Yikes! Why bother with all that? Here's the simple point-and-shoot way. First you'll have to open the "summary" workbook and ALL the "closed" workbooks. Then, wherever you want a summary entry:
- Click the cell in the "summary" workbook where you want the entry.
- Type "=" (th equal sign) so you're starting to enter a formula.
- Use Ctrl-F6 to switch to the "closed" workbook whose entry you want to copy.
- Click that "closed"-workbook cell. It will then be surrounded by moving dashes, and a huge formula will appear looking like 'C:\path\path\[closed_workbook.xls]Sheet1'!$A$1
- Hit Enter. You'll be switched back to the "summary" workbook, and the entry will be there.

Simpler yet, if you want just the values and don't care about links to the closed workbooks:
- Switch to the "closed" workbook
- Select the cell you want to copy, and hit Ctrl-C.
- Switch to the "summary" workbook.
- Use Edit / Paste Special to paste onlyl values.

Extra data (specific cells) from various close workbook

mullas007

New Member

Excel Facts

jasmith4

Active Member

Similar threads

Forum statistics

Share this page

Extra data (specific cells) from various close workbook

mullas007

New Member

Excel Facts

jasmith4

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock