Import user selected columns from a text file

Hi,

I'm trying to write a macro that imports a tab delimited text file. The issue I'm having is that the files tend to be pretty big, so I want it to only import the reference columns (from 1 to 6) and channel columns (from 1 to 16) selected by the user.

I've managed to write an inelegant if statement that works for the channel select option but adding in the reference option would give almost 100 different scenarios, so I figured there has to be a better way to do this

I've attached a text file showing roughly how the data is presented in the text file (there are a lot of useless columns but this is the way it's given to me for now) and a file with the macro that I've been using for one option.

Hope someone can help.

Cheers.

Hi

Do all the columns that have DISABLED as the heading, really have that as the heading, or do they have something meaningful that you have disguised for the example purposes?

rylo

Hi rylo,

No, DISABLED is not the actually heading. Most of the columns with that are actually rarely or never used, but there are a few that my boss would prefer I not make public. So to make things less confusing I just disguised all the headers that aren't needed for this issue.

aarho

Hmmm

I was thinking about using adodb to bring in the required columns, but unfortunately the first row isn't a heading row, and as there are spaces in the heading names (even if it was the first row), then it wouldn't work.

Back to the drawing board....

rylo

Hi

OK, this is a bit clunky but here goes.

1) Home!B5 - create a data validation dropdown list referencing range U2:U16. This will enable you to directly select the channel heading.
2) Home!D5 - create a data validation dropdown list referencing range T2:T9

Make selections in both of those cells. Then try

Sub aaa()
  Dim OutSH As Worksheet
  Sheets("Home").Select
  File2 = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Select the file to import")
  If File2 = "false" Then Exit Sub

  Sheets.Add before:=Sheets(1)
  ActiveSheet.Name = Sheets("Home").Range("A1").Value
  Set OutSH = ActiveSheet
  Range("A1:D1").Value = Array("Date", "Time", "Setpoint 1", "Setpoint 2")
  Range("E1").Value = Sheets("Home").Range("B5").Value
  Range("F1").Value = Sheets("Home").Range("D5").Value
  
  Workbooks.OpenText Filename:=File2, Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
        16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _
        Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array( _
        29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), _
        Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array( _
        42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), _
        Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array( _
        55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), _
        Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array(67, 1), Array( _
        68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array(72, 1), Array(73, 1)), _
        TrailingMinusNumbers:=True
        
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = Cells(3, Columns.Count).End(xlToLeft).Column
    Range(Range("A3"), Cells(lastrow, lastcol)).AdvancedFilter Action:=xlFilterCopy, copytorange:=OutSH.Range("A1:F1")
    
    ActiveWorkbook.Close savechanges:=False
End Sub

You will have to update it for the extra columns that you want as I don't have access to the real headings, but hopefully you will get what I'm trying to do. It does open the file completely in Excel, but then shuts it down again. If you turn off the display, then it really shouldn't be a problem.

Good luck

rylo

rylo! You're awesome!
Once again you solved my problem and in a way that should help improve some other stuff I've done and make them much more efficient!

I was hoping you could walk me through this one a bit. I'm still a little green when it comes to this stuff (I'm sure that's pretty obvious), so I think maybe there's a step I'm not seeing or understanding.

As I see it, this is how the macro you wrote works:


1- Prompt to select file
2- Insert a new sheet with name the value of cell A1
3- Set table headers
4- Open the selected .txt file
5- Get data table dimensions (lastrow, lastcol)?
6- Apply a filter and copy to A1:F1 in the new sheet
7- Close the selected file

I think my confusion is coming at the filter stage. I'm just not seeing where or how it's telling it to go to the ref and ch columns selected. It clearly is because it's working great, but I'm not seeing it.

Thanks

aarho

aarho

You have got the steps spot on.

With the filter, it puts in the headings into the output sheet, inlcuding the headings selected in the main file. As you have the headings that are in the raw data file, that is used by the filter when determining what to output from the filter process. I know that there are a couple of headings missing, but as I don't have the real headings in the raw file, I couldn't include them in the output. I think there are 2 (or 3) missing fields, so if you determine what those headings are you can include them in the fixed component, then put the user selected ones further over. You will have to adjust the output placement of the user selected ones.

If you open the raw text file in excel, you will see that it puts the headings into row 3.

If that doesn't make sense, then build yourself a little test file with some dummy headings and data, and see just how the filter works. That may make things clearer.

rylo

I think I get it now. Thanks a lot for all your help!

aarho