Problem opening/reading text files into worksheet 1

[JoeSchepis]

Hi,

I am creating an Excel workbook as a template. In this template, I have 6 worksheets defined. This first worksheet is a summary and contains VBA code (not working so well). In this code, I want to select and read in a text file (file extension "DET") and fill in the data into one of the worksheets (the same one each time).

After a Sheets.Select statement to get me on the right sheet, I have used the Application.GetOpenFileName (with the "*.DET" qualifier) to select the file. If I use "Workbooks.Open vFile", I get a new workbook (makes sense). If I use the "With Selection.QueryTable" technique, I am forced into another file open dialog box looking for TXT files. Of course, if I select a DET file that I want, it reads it into the worksheet properly.

Is there any way to place the text contents of vFile (filename.DET) into an exisitng worksheet without having to negotiate file extensions in the Query.Select method?

An example would help this newbie.
Joe

 

[bubba100]

As we can't guess at your code.. I use this to import a txt file, warts and all.
Please notice how many times the term SELECT is used (hint).

Dim strFileName As String
Dim fileToOpen As String
Dim sName As String
Dim SelectCancel As Boolean
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt", , _
"Select The File To Convert")
strFileName = fileToOpen
On Error Resume Next
If strFileName <> False Then
SelectCancel = False
Else: SelectCancel = True
End If
If SelectCancel = True Then
MsgBox ("You Selected Cancel.")
Exit Sub
End If
Err.Clear

sName = Split(fileToOpen, "\")(UBound(Split(fileToOpen, "\")))
sName = Split(sName, ".")(0)
'With ActiveSheet.QueryTables.Add(Connection:= _
'"TEXT;C:\My Documents\Collins_text_file\SchedCDIN102008.txt", Destination:= _
'Range("A1"))

With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"Text;" & strFileName & "", Destination:=Range("A2"))


'With ActiveSheet.QueryTables.Add(Connection:= _
"Text;" & strFileName & "", Destination:=Range("A2"))
.Name = sName
'.Name = "SchedCDIN102008"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 1, _
1, 1, 1)
' .TextFileFixedColumnWidths = Array(2, 6, 47, 35, 33, 2, 9, 9, 7, 8, 60, 35, 20, 2, 6, 12, 8 _
, 2, 15, 23, 6, 13, 15)
.TextFileFixedColumnWidths = Array(2, 6, 60, 35, 20, 2, 9, 9, 7, 8, 60, 35, 20, 2, 9, 9, 8 _
, 2, 25, 13, 13, 13, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
'Selection.EntireRow.Insert
Worksheets("Sheet1").Range("A:B").Delete shift:=xlToLeft
'Worksheets("Sheet1").Range("A1").Value = "Shipped To State"
'Worksheets("Sheet1").Range("B1").Value = "Month"
Worksheets("Sheet1").Range("A1").Value = "ShipFromName"
Worksheets("Sheet1").Range("B1").Value = "ShipFromBlockLine2"
Worksheets("Sheet1").Range("C1").Value = "ShipFromCity"
Worksheets("Sheet1").Range("D1").Value = "ShipFromState"
Worksheets("Sheet1").Range("E1").Value = "ShipFromZip"
Worksheets("Sheet1").Range("F1").Value = "FEIN2"
Worksheets("Sheet1").Range("G1").Value = "IL License"
Worksheets("Sheet1").Range("H1").Value = "IBT"
Worksheets("Sheet1").Range("I1").Value = "Ship To Name"
Worksheets("Sheet1").Range("J1").Value = "ShipToBlockLine2"
Worksheets("Sheet1").Range("K1").Value = "ShipToBlockCity"
Worksheets("Sheet1").Range("L1").Value = "ShipToBlockRegion"
Worksheets("Sheet1").Range("M1").Value = "ShipToBlockPostalCode"
Worksheets("Sheet1").Range("N1").Value = "FEIN"
Worksheets("Sheet1").Range("O1").Value = "Ship Date"
Worksheets("Sheet1").Range("P1").Value = "How Shipped"
Worksheets("Sheet1").Range("Q1").Value = "Inv Number"
Worksheets("Sheet1").Range("R1").Value = "Packs 20"
Worksheets("Sheet1").Range("S1").Value = "Packs 25"
Worksheets("Sheet1").Range("T1").Value = "Other"
Worksheets("Sheet1").Range("U1").Value = "Sticks"
Worksheets("Sheet1").Range("V1").Value = "Tax Paid"
Worksheets("Sheet1").Range("A1").Select
If Worksheets("Sheet1").CommandButton1.Visible = True Then
Worksheets("Sheet1").CommandButton1.Visible = False
End If
ActiveWorkbook.SaveAs Filename:="C:\My Documents\" & sName & ".xls"
fileToOpen = ""
strFileName = ""

End Sub

 

[SkipVought]

Actually, if you IMPORT the SAME file into the SAME sheet each time, you do not need VBA in order to refresh the QueryTable.

However, if you import the same data STRUCTURE from DIFFERENT files, then all you need is the GetOpenFileName to get filename and...

with YourSheetObject.QueryTables(1)
   .Connection = "TEXT;" & [b]filename[/b] & ";"
   .refresh false
end with

or for Excel 1007...

with YourSheetObject.ListObjects(1).QueryTable
   .Connection = "TEXT;" & [b]filename[/b] & ";"
   .refresh false
end with

assuming that you have ONLY ONE querytable on your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeSchepis]

Thanks to both Bubba100 and Skip. You both have a good appreciation for the problem but it persists so let me explain a little further...

First, Skip is correct. I want to read the same data structure from multiple files to statistically and graphically analyze/review the data. The text data files come in pairs: 1 file for starting torque, 1 file for running torque. The input file name convention will be determined by each user but the extension is always "DET" produced by the data acquisition application. (That application will only write out starting torque or running torque into separate files. The file extension is not an option and I'd like to leave it as is so that other users don't end up messing things up by changing file names, accidentally deleting the wrong files, etc.)

Second, before my first posting here, this was my VBA code based on copying a recorded macro I used to read in a data file put into "CommandButton1 Click":

Sheets("Starting Torque (Raw)").Select
With Selection.QueryTable
.Connection = _
"TEXT;C:\Documents and Settings\jschepis\My Documents\LDCM TIRS\SSM\SSM Flight bearing data\SN004_STARTING.DET"
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

However, you can see that the macro captures a specific file name. The import itself works fine but the routine does not provide the flexibility I need. So, on my own, I looked into ways to pass a variable carrying the file name and played with the GetOpenFilename technique shown here:


Private Sub CommandButton1_Click()
'Button to read in starting torque values from Vibrac DET file

Dim vFile As Variant

Sheets("Starting Torque (Raw)").Activate

'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("VIBRAC Files (*.DET), *.DET", 1, "Select VIBRAC Starting Torque Detail File", "Open", False)

'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
End If

'Open selected file
' Workbooks.Open vFile
Workbooks.OpenText Filename:=vFile, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote

MsgBox ("Command Button 1 pressed: " & vFile)

End Sub

Clearly, I was forcing Excel to fail by using the Workbooks.Open type calls. The result of these codes was to create a new workbook with my text file happily sitting in there (in the wrong workbook!). So at this point, I began to incorporate your codes.

Using your code Bubba, I get a file open dialog asking for a text file. When I redirect to *.DET and select the file I want, no action seems to take place. This is the code that I used (your comment lines removed):

' This code from Bubba100 (Tech-tips.com)
Dim strFileName As String
Dim fileToOpen As String
Dim sName As String
Dim SelectCancel As Boolean
fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt", , _
"Select The File To Convert")
strFileName = fileToOpen
On Error Resume Next
If strFileName <> False Then
SelectCancel = False
Else: SelectCancel = True
End If
If SelectCancel = True Then
MsgBox ("You Selected Cancel.")
Exit Sub
End If
Err.Clear

sName = Split(fileToOpen, "\")(UBound(Split(fileToOpen, "\")))
sName = Split(sName, ".")(0)

With Worksheets("Running Torque (Raw)").QueryTables.Add(Connection:= _
"Text;" & strFileName & "", Destination:=Range("A2"))
End With

I tried both "Running Torque (Raw)" and "Sheet3" in the With Worksheets call. By the way, if I use "VIBRAC files (*.DET), *.det" in the GetOpenFilename, the code properly lists the files I'm interested in. In all cases, the selected file just doesn't seem to be imported anywhere. Do you have any thoughts on why no data is imported?

Using your Excel 2003 code Skip, placed within my code:

Dim vFile As Variant

Sheets(4).Activate

'Showing Excel Open Dialog Form
vFile = Application.GetOpenFilename("VIBRAC Files (*.DET), *.DET", , "Select VIBRAC Starting Torque Detail File", "Open", False)

'If Cancel then exit
If TypeName(vFile) = "Boolean" Then
MsgBox ("File Read Canceled by the User.")
Exit Sub
End If

'Open selected file
With Selection.QueryTable
.Connection = "TEXT;" & vFile & ";"
.Refresh True
End With

With this code, I also get a proper open file dialog so I can select a DET file I'm interested in. After selecting "Open" though, I get a Querytable-like import text file dialog box with the previously selected file entered. If I select "Import" the file is properly read in. I think this is the path forward but why am I asked for to respond to two file open dialogs? In other words, why doesn't the "vFile" in the Selection.QueryTable connection work without opening another dialog box to request the same file a second time? Is there a way to force a Selection.QueryTable-like function without opening a second dialog box?

Thanks much to you both!
Joe

 

[Gruuuu]

You can change your Data Range Properties (right click on your data) and uncheck "Prompt for file name on refresh"
There's a few other options you may want to play with in there too, such as "Fill down formulas in columns adjacent to data" (this only works on columns directly to theright of your data, annoyingly)

 

[SkipVought]

This works. Use YOUR sheet obejct...

    'Open selected file
    With Sheet1.QueryTables(1)
        .Connection = "TEXT;" & vFile
        .TextFilePromptOnRefresh = False
        .Refresh = False
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeSchepis]

Gruuuu, I unchecked as you suggested but the the code crashes at the ".Refresh True" statement. Since I'm new at this, I changed to ".Refresh False" but I still get an error at that line. In both cases, the selected file is not imported.

Joe

 

[SkipVought]

As noted above, REMOVE the SEMICOLON following your vFile.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeSchepis]

Skip,

Yes, I caught that! This fix works! In the end, the final code is:

    Private Sub CommandButton1_Click()
    'Button to read in STARTING torque values from Vibrac DET file

    Dim vFile As Variant

    'Showing Excel Open Dialog Form
    vFile = Application.GetOpenFilename("VIBRAC Files (*.DET), *.DET", , "Select VIBRAC Starting Torque Detail File", "Open", False)

    'If Cancel then exit
    If TypeName(vFile) = "Boolean" Then
        MsgBox ("File Read Canceled by the User.")
        Exit Sub
    End If

    Sheets(4).Activate

    'Open selected file (per SkipVought's 2nd reply)
    With Sheets("Starting Torque (Raw)").QueryTables(1)
    .Connection = "TEXT;" & vFile
    .TextFilePromptOnRefresh = False
    .Refresh False
    End With

    Sheets(1).Activate

    'At end of code, flag file name agreement cell color
    If Sheets(1).Range("B48") = "Yes" Then
        Range("B48").Interior.ColorIndex = 4
    Else
        Range("B48").Interior.ColorIndex = 3
    End If

    End Sub

With your approach, I don't know if I need the Sheets.Activate call to drop the data into the correct sheet, do I?

Can you recommend a good reference text for the Excel VBA containers, handles, objects, etc? This is similar enough to VB5 to cause great headaches! Excel and VBA Help files are not very useful.

Thanks again and to Bubba100 and Gruuuu as well,
Joe

 

[SkipVought]

If you reference your objects explicitly, there is no need to activate or select sheets and ranges.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

There are many. I like John Walkenbach books. I have several.

http://www.j-walk.com

But frankly, the best reference I have found is Tek-Tips! My learning curve slowly increased, until I began participating here, and it grew exponentially.

After nearly 10 year participating at Tek-Tips, I am still learning from other members!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JoeSchepis]

Thanks Skip! You're my vote for TipMaster of the week!

 

[mscallisto]

I agree, John Walkenbach books are pretty good "Bibles" for VBA although they're not cheap but worth it.

And Tek-Tips allows one to "Add this thread to your archive" when you find interesting discussion and/or code snippets, very handy for future reference.