Microsoft Office Forums - View Single Post - [Solved] macro, data import from the ONLY text file in current folder

ue418 · 05-17-2012, 11:28 AM

I have a macro in Excel 2000 that I recorded. All it does is import a text file into cell A3 of the current sheet, while setting certain desired "delimeter" options, etc. Here is the macro:

Code:

Sub Import_Data() 
     '
     ' Import_Data Macro
     ' Macro recorded 5/17/2012 by Rick_9Feb2012
     '
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;C:\Folder_Holds_TextFile\My_Text_File.tx t", Destination:=Range("A3")) 
        .Name = "My_Text_File" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlOverwriteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = xlWindows 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = False 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = True 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) 
        .Refresh BackgroundQuery:=False 
    End With 
    Application.Left = 2.5 
    Application.Top = 1 
End Sub

You can see that the code grabs the text file called "My_Text_File.txt", located at: C:\Folder_Holds_TextFile\. The only change I want is for the code to somehow grab the ONLY text file in the folder where the Excel spreadsheet is located (and opened from). I'm hoping that when you open a spreadsheet, there is some parameter deep within that specifies the folder it was opened from? If so, then I can use that parameter in place of "C:\Folder_Holds_TextFile\". Then, I just need to remove the direct specification of the text file name. I just want it to grab the ONLY text file in that same folder. Each time the spreadsheet is opened, that text file (and the folder containing the spreadsheet and text file) will be different...I don't want the user to have to find and type in the name and/or folder. Can someone help me do this, with just a few changes to this code? Thanks ahead of time!
Rick

UPDATE: it looks like the following code might (if I throw it in at the end of my subroutine above) give me the CURRENT path. That is, the folder location where the workbook was opened from. Of course, this is the SAME folder where my lone text file resides.

Dim sPath As String
sPath = ActiveWorkbook.Path

If this (or some other method) works, then all I have remaining is to reference that text file of an "unknown" name. Can I just replace the "My_Text_File.txt" with some wildcard thing, like **.txt?? I need something like that, to get that lone text file, no matter what its name is. Thanks!

Another UpDate!! I have added these four lines at the top (I labeled LINE 1 thru LINE 4), and changed LINE 5 and LINE 6 accordingly. I think it almost works, but there is an error around LINE 5. My macro now looks as:

Code:

Sub Import_Data() 
     '
     ' Import_Data Macro
     ' Macro recorded 5/17/2012 by Rick_9Feb2012
     '
    Dim sPath As String, TextPath_Tmp As String, TextFilePath As String LINE 1 
    sPath = ActiveWorkbook.Path LINE 2 
    TextPath_Tmp = Dir(sPath & "\" & "*.txt") LINE 3 
    TextFilePath = sPath & "\" & TextPath_Tmp LINE 4 
 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;TextFilePath", Destination:=Range("A3")) LINE 5 
        .Name = TextPath_Tmp LINE 6 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlOverwriteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = xlWindows 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = False 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = True 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) 
        .Refresh BackgroundQuery:=False 
    End With 
    Application.Left = 2.5 
    Application.Top = 1 
End Sub

Those quotation marks around the entire TEXT line of the macro (LINE 5 of latest version) cause a problem when I substitute the variable TextFilePath for the explicitly-written path that you see in the original macro. I think something needs to be done with those quotations. Please help me with this if you can. Thanks.

Rick