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]

The import works but the cells that call on the imported data reset each time I change station names and I get a REF# error.

This is the same sort of error that occurs when you paste on top of a cell that is previously referenced in some other cell.

 

[ZVI]

The import works but the cells that call on the imported data reset each time I change station names and I get a REF# error.

This is the same sort of error that occurs when you paste on top of a cell that is previously referenced in some other cell.

It was because of columns deleting usage in the code.
Ok, try this:

' The code of "Input" sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
  
  ' --> User settings, change to suit
  Const ChooseStationCell = "D4"    ' Validation list cell address
  Const FileNameCell = "D5"         ' Vlookup formula cell address
  Const FileNameExt = "CSV"         ' External data file extention
  Const FileFolder = "C:\Temp"      ' Folder with external data files
  Const LinesDelim = vbLf           ' Lines delimiter of CSV file
  Const DestSheet = "Data"          ' Destination sheet name
  Const ImportedColumns = "F,H"     ' Columns to be imported"
  ' <-- End of User settings
  
  Dim FileName$, FileNo%, r&, i&, txt$, a, b(), x
  
  If Intersect(Target, Range(ChooseStationCell)) Is Nothing Then Exit Sub
  Sheets(DestSheet).UsedRange.ClearContents
  FileName = FileFolder & IIf(Right(FileFolder, 1) <> "\", "\", "") & Range(FileNameCell) & "." & FileNameExt
  If Dir(FileName) = "" Then Exit Sub
  
  ' Copy text of CSV-file  into variable txt
  FileNo = FreeFile
  Open FileName For Input As #FileNo
  txt = Input(LOF(FileNo), #FileNo)
  Close #FileNo
  
  ' Convert txt to the lines array a()
  a = Split(txt, LinesDelim)
  
  ' Copy a() into trasposed b()
  ReDim b(0 To UBound(a), 1 To 1)
  For Each x In a
    b(r, 1) = a(r)
    r = r + 1
  Next
  
  ' Freeze on screen, events, calculations (speeding up)
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
  End With
  
  ' Copy b() to the destination sheet with TextToColumns conversion
  With Sheets(DestSheet).Cells(1, 1).Resize(UBound(a) + 1)
    .Value = b()
    .TextToColumns Destination:=.Cells(1, 1), Comma:=True, FieldInfo:=Array(1, xlMDYFormat)
    .Rows(2).Columns.AutoFit
  End With
  
  ' Delete all columns but ImportedColumns
  With Sheets(DestSheet).UsedRange
    For Each x In Split(ImportedColumns, ",")
      i = i + 1
      .Columns(x).Copy Destination:=.Columns(i)
    Next
    .Range(.Columns(i + 1), .Columns(.Columns.Count)).ClearContents
  End With

  ' Unfreeze screen, events, calculations
  With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
  End With
  
End Sub

 

[ZVI]

One more correction.

Replace this line of code

    .Range(.Columns(i + 1), .Columns(.Columns.Count)).ClearContents

By that one:

    If i < .Columns.Count Then .Range(.Columns(i + 1), .Columns(.Columns.Count)).ClearContents

 

[User Name]

He shoots, he scores! Awesome!!! I implemented the first version you suggested before the correction. The first implementation worked but I adjusted the code per your suggestion. Whenever you want those chocolates just PM me your address and you've got them.

 

[ZVI]

He shoots, he scores! Awesome!!! I implemented the first version you suggested before the correction. The first implementation worked but I adjusted the code per your suggestion. Whenever you want those chocolates just PM me your address and you've got them.

Glad that worked for you!
Cheers,