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]

Not trying to steal your thunder vbacoder but I have a follow up question as well.

The default file system that I'm dealing with is csv. I've gotten this code to work but only with xlsm files. I've tried adjusting this value:

Const FileNameExt = "csv"

... but it doesn't work. Any suggestions?

 

[ZVI]

Not trying to steal your thunder vbacoder but I have a follow up question as well.

The default file system that I'm dealing with is csv. I've gotten this code to work but only with xlsm files. I've tried adjusting this value:

Const FileNameExt = "csv"

... but it doesn't work. Any suggestions?

I've just realized that your external files are not XLS* but CSV files!
Ok, what separator symbol is used in CSV? Is it tab, comma or semicolon or something else?

 

[ZVI]

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

In the code of post #5 replace (2 times) ThisWorkbook.Path by the path to required folder, for example by: "C:\Temp"

 

[User Name]

here is an example file:


http://www.4shared.com/file/aeEtgLBm/690150TY.html

 

[ZVI]

Thank you for the example - it saves the analysing time.

The CSV files are not workbooks itself, there are no worksheets & cells in it.
CSV file is the Comma Separated Values text file.
But comma is one of the possible separator chars.

In you example of CVS file I’ve found more than 8000 rows with 2 title rows.
7 columns are in the title row #1 and 68 columns are in title row #2
Value types of the title row #1 are different to the title row #2.
These mean incompatibility with ADO-method because of mixed (unstructured) data using.

One of the possible solutions can be as follows:
1. Read text file into the string variable txt
2. Parse string variable txt into the text lines array a()
3. Copy a() into transposed b() array
4. Copy b() array into the 1st column of destination sheet
5. Apply TextToColumns conversion

The code below reflects such method:

' 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
  ' <-- End of User settings
  
  Dim FileName$, FileNo%, r&, 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
  
  ' 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)
    .NumberFormat = "mm/dd/yy;@"
    .Rows(2).Columns.AutoFit
  End With
  
End Sub

Regards

 

[User Name]

I finally got back around to this problem and tried out your code. It works like a charm!

Is there a way to specify which columns get imported? Say I only need columns E & H? Would specifying less data speed up the transfer?

 

[tusharm]

Use MS Query to build a parameterized query. SQL (the output of MS Query) works with a CSV file. For an example of how to use MS Query as well as of a parameterized query, see

Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

and

Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

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?

 

[ZVI]

I finally got back around to this problem and tried out your code. It works like a charm!

Is there a way to specify which columns get imported? Say I only need columns E & H? Would specifying less data speed up the transfer?

Below is the code for importing of the columns defined in the constant ImportedColumns.
Freezing of screen updating, events and recalculations can speed up the code a bit.

' 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&, 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)
    .NumberFormat = "mm/dd/yy;@"
    .Rows(2).Columns.AutoFit
  End With
  
  ' Delete all columns but ImportedColumns
  With Sheets(DestSheet).UsedRange
    .Columns.Hidden = False
    For Each x In Split(ImportedColumns, ",")
      .Columns(x).Hidden = True
    Next
    .EntireColumn.SpecialCells(xlCellTypeVisible).Delete
    .Columns.Hidden = False
  End With
  
  ' Freeze off screen & events
  With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
  End With
  
End Sub


Sub test()
  
  
  Dim x
  'Sh3_Data.Columns("F").Hidden = False 'True
  Const ImportedColumns = "F,H"
  With Sh3_Data.UsedRange '.EntireColumn
    '.Columns("A").Select
    For Each x In Split(ImportedColumns, ",")
      .Columns(x).Hidden = True
    Next
    
    .EntireColumn.SpecialCells(xlCellTypeVisible).Delete
    .Columns.Hidden = False
  End With
End Sub

 

[ZVI]

Use MS Query to build a parameterized query. SQL (the output of MS Query) works with a CSV file. For an example of how to use MS Query as well as of a parameterized query, see

Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

and

Cascading queries
http://www.tushar-mehta.com/excel/newsgroups/cascading_dropdowns/index.html

Hi Tushar,

As I’ve mentioned in post #15 there are not structured data in the given example.
Two top title rows have the different amount of items with not the same data types.
The second row is the real title row for the data.
Not sure how MS Query can be used in this case as it recognize the 1st row as the header (title) one.

If you know the tricks could you please explain it?

Regards,

 

[ZVI]

Below is the code for importing of the columns defined in the constant ImportedColumns
...

...
Sub test()
...
End Sub

Please ignore Test() subroutine - it's the debugging garbage