Extract Data From Closed Workbooks

User Name

Spammer
Joined
Aug 10, 2010
Messages
182
http://www.4shared.com/file/Jfn8Tejg/Extract_Closed_Data_Problem.html

First time using the 4shared site. Hope the link works.

On the input page you select a Station name from the drop down list (e.g. SAN BERNADINO/NORTON AFB). This selection automatically populates the filename cell (e.g. 724837_2005_solar) just below it. I want the first three columns and three rows from this file (724837_2005_solar) to populate on my Data tab starting at A1.

I've used Concatenate() along with Indirect() but this only works when the file is open. I've tried Indirect.ext() but that's not working either.

I'd like to be able to extract the required data from a closed workbook. Oh... the files are .csv files. Could that be causing problems? Any ideas?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

User Name

Spammer
Joined
Aug 10, 2010
Messages
182
Thank you... I'm sure this question has been asked many times. I have tried the ADO technique already but I'll try again knowing this is the suggested route.
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,800
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi,
Copy the code into module of Sheet "Input" and try to change the Station value in D4:
Rich (BB code):

' Put all the code into "Input" sheet's module

Private Sub Worksheet_Change(ByVal Target As Range)
  
  Const ChooseStationCell = "D4"
  Const FileNameCell = "D5"
  Const FileNameExt = "xlsx"
  Const ExternalShName = "Sheet1"
  
  If Intersect(Target, Range(ChooseStationCell)) Is Nothing Then Exit Sub
  
  On Error Resume Next
  With Sheets("Data").Range("A1:C3")
    .ClearContents
    If Dir(ThisWorkbook.Path & "\" & Range(FileNameCell) & "." & FileNameExt) <> "" Then
      .FormulaR1C1 = "='" & ThisWorkbook.Path & "\[" & Range(FileNameCell) & "." & FileNameExt & "]" & ExternalShName & "'!RC"
      .Value = .Value
    End If
  End With
  
End Sub
It is assumed that external workbooks are in the same folder as the Extract Closed Data Problem.xlsm
BTW: save your master workbook with code as XLSM !

Regards
 
Last edited:

User Name

Spammer
Joined
Aug 10, 2010
Messages
182
This is exactly what I've posted into the VBA window. I made sure to save the file as .xlms. I changed the FileNameCell, ChooseStationCell, ExternalShName (which is also the same as the FileNameCell) and the Range as appropriate. I know everything is here but I'm getting Bupkis. I've never done anything close to this. I learned VLOOKUP two days ago but I'd like to take my program up to the next level. The Directory path is C:\Data. Could you highlight the fields that need further editing?

HTML:
Private Sub Worksheet_Change(ByVal Target As Range)
  
  Const ChooseStationCell = "B17"
  Const FileNameCell = "B16"
  Const FileNameExt = "xlsm"
  Const ExternalShName = "B16"
  
 If Intersect(Target, Range(ChooseStationCell)) Is Nothing Then Exit Sub
  
  On Error Resume Next
  With Sheets("Data").Range("A1:AN8761")
    .ClearContents
    If Dir(ThisWorkbook.Path & "\" & Range(FileNameCell) & "." & FileNameExt) <> "" Then
      .FormulaR1C1 = "='" & ThisWorkbook.Path & "\[" & Range(FileNameCell) & "." & FileNameExt & "]" & ExternalShName & "'!RC"
      .Value = .Value
    End If
  End With
  
End Sub
 

User Name

Spammer
Joined
Aug 10, 2010
Messages
182

ADVERTISEMENT

Aha!!! It worked but there's a problem. Do the workbooks that I'm extracting the data from have to be xlsm? Can I open xls files? I'll save them as xlsm if need be but I'd just like to know if this is a requirement?
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,800
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Workbook with macro should be saved as XLSM (Excel 2007+ Macro Enable workbook) or XLSB (Excel 2007+ Binary Worbook). It can be also saved as XLS or XLA.
But file format of external data workbooks doesn’t matter: can be one of all possible for Excel, including XLSX or XLS.
 

User Name

Spammer
Joined
Aug 10, 2010
Messages
182
Hmmm... Well I'll tell you... This is just brilliant. It works... but, it only works if the sheets I'm extracting from are xlsm. I've switched between the two formats (in the code above - i.e. Const FileNameExt = "xlsx"/"xls") several times and am getting the same result. I don't understand the problem?

But it's not really a problem because I know the solution. I have three follow up questions.

1. Is there something about the code above that is preventing opening xls files?
2. Is there a group highlight method of reformatting xls files as xlsm files?
3. What kind of chocolates do you like and what's your address. PM me and you've got some chocolates man.

I am tremendously grateful for your help on this problem. It is a great trick to know.

P.S. Does anyone have any advice on building a photovoltaic simulator? Does anyone know of an open source xls file that can be referenced? The first part of my process is cracking into the raw data. From here I will have to build the equations. I have some cheat codes that do a quick and dirty solution to the problem but ultimately I want something that isn't a cheat. If anyone knows of an open source photovoltaic simulator that would be double chocolate worthy.

P.P.S. A photovoltaic simulator= a program that simulates the power generated by a solar power system.
 
Last edited:

vbacoder

Active Member
Joined
Jul 7, 2007
Messages
354
It is assumed that external workbooks are in the same folder as the Extract Closed Data Problem.xlsm
BTW: save your master workbook with code as XLSM !
Regards

In my case, I'm trying to access data cells in external worksheets that are in different folders to the current worksheet. Is there any way for the same technique to work or is there an alternative?

http://www.mrexcel.com/forum/showthread.php?p=2409896#post2409896

Many thanks,

vcoder
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top