macro, data import from the ONLY text file in current folder

ue418 · #1 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

ue418 · #2 05-17-2012, 05:22 PM

The TEXT: line should read:

(Connection:="TEXT;" + TextFilePath, Destination...blah blah

I knew it had something to do with those quotation marks near TEXT. Maybe this will help somebody...someday. Thanks.

ue418 · #3 06-16-2012, 02:18 PM

Hello Again. Regarding the macro in the above posts, I would like for the macro to place the data in A3 of a specific sheet. Let's say it is named "Sheet1". The current line is of the form:

With ActiveSheet.QueryTables...

Can I simply change "ActiveSheet" in that line to "Sheet1"? I have a feeling that is too obvious/simple, and there is some special format I must use? The deal is, the macro button (unfortunately) shows up on every sheet of the workbook. As long as the user clicks the macro when he is on "Sheet1", the data (correctly) goes into Sheet1. But if he clicks the macro button when he is on, say, Sheet 2, then the data will go into Sheet 2, since it is the active sheet. I want the data to ALWAYS go into Sheet 1. If anyone can help me out, I would greatly appreciate it.

Rick

UPDATE:
I substituted Sheets("Sheet1") for ActiveSheet in that line, and I got a VB run-time error saying "the destination range is not on the same worksheet that the Query table is being created on". That happened when I clicked the macro on Sheet 2. When I click the macro on Sheet 1, everything works fine. Can I not do it from a different sheet? I would appreciate any help you could give me.

ue418 · #4 06-16-2012, 02:44 PM

I had to change the "Range" part of the command from =Range("A3") to =Range("Sheet1!A3"). Now it works fine, even when I click the macro on Sheet2! Thanks for reading.

Rick

Wabbits · #5 12-08-2016, 08:23 AM

Hi Rick,

Thank you for posting this thread, your updates have helped me work out a similar problem today.

Sarah

ue418 · #6 10-28-2017, 12:52 PM

You're welcome Sarah. Just as I had hoped, it did help somebody, 3 or 4 years down the line.

Rick

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to import multiple text file data into word	mizankabir	Word VBA	7	09-15-2023 10:27 PM
PST Import - "Not a Personal Folder File"	stucraig.uk	Outlook	2	03-30-2011 04:00 PM
Word Macro: Save file as text with current file name	jabberwocky12	Word VBA	2	10-22-2010 12:23 PM
Import Text from File - Line Spacing	marshalx	Word	2	10-28-2009 02:37 AM