Extract Data From Closed Workbooks

[User Name]

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?

 

[User Name]

Bueller? Bueller?

 

[Smitty]

Welcome to the Board!

Check out Ole Erlandsen's ADO/DAO page.

HTH,

 

[User Name]

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]

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

' 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

 

[User Name]

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?

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]

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]

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]

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.

 

[vbacoder]

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