Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-17-2012, 11:28 AM
ue418 ue418 is offline macro, data import from the ONLY text file in current folder Windows 7 32bit macro, data import from the ONLY text file in current folder Office 2000
Novice
macro, data import from the ONLY text file in current folder
 
Join Date: May 2012
Posts: 17
ue418 is on a distinguished road
Default macro, data import from the ONLY text file in current folder

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

Last edited by macropod; 06-16-2012 at 03:11 PM. Reason: Added code tags & formatting
Reply With Quote
  #2  
Old 05-17-2012, 05:22 PM
ue418 ue418 is offline macro, data import from the ONLY text file in current folder Windows 7 32bit macro, data import from the ONLY text file in current folder Office 2000
Novice
macro, data import from the ONLY text file in current folder
 
Join Date: May 2012
Posts: 17
ue418 is on a distinguished road
Default I got it!

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.
Reply With Quote
  #3  
Old 06-16-2012, 02:18 PM
ue418 ue418 is offline macro, data import from the ONLY text file in current folder Windows 7 32bit macro, data import from the ONLY text file in current folder Office 2000
Novice
macro, data import from the ONLY text file in current folder
 
Join Date: May 2012
Posts: 17
ue418 is on a distinguished road
Default A related question on this macro

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.

Last edited by ue418; 06-16-2012 at 02:35 PM. Reason: Have new information
Reply With Quote
  #4  
Old 06-16-2012, 02:44 PM
ue418 ue418 is offline macro, data import from the ONLY text file in current folder Windows 7 32bit macro, data import from the ONLY text file in current folder Office 2000
Novice
macro, data import from the ONLY text file in current folder
 
Join Date: May 2012
Posts: 17
ue418 is on a distinguished road
Default Okay, I got it...

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
Reply With Quote
  #5  
Old 12-08-2016, 08:23 AM
Wabbits Wabbits is offline macro, data import from the ONLY text file in current folder Windows XP macro, data import from the ONLY text file in current folder Office 2016
Novice
 
Join Date: Dec 2016
Posts: 1
Wabbits is on a distinguished road
Default

Hi Rick,

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

Sarah
Reply With Quote
  #6  
Old 10-28-2017, 12:52 PM
ue418 ue418 is offline macro, data import from the ONLY text file in current folder Windows 7 32bit macro, data import from the ONLY text file in current folder Office 2007
Novice
macro, data import from the ONLY text file in current folder
 
Join Date: May 2012
Posts: 17
ue418 is on a distinguished road
Default Better Late than Never

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

Rick
Reply With Quote
Reply

Thread Tools
Display Modes


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
macro, data import from the ONLY text file in current folder PST Import - "Not a Personal Folder File" stucraig.uk Outlook 2 03-30-2011 04:00 PM
macro, data import from the ONLY text file in current folder Word Macro: Save file as text with current file name jabberwocky12 Word VBA 2 10-22-2010 12:23 PM
macro, data import from the ONLY text file in current folder Import Text from File - Line Spacing marshalx Word 2 10-28-2009 02:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft