Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parent Folder (Excel 2000)

    Hi all, how do I insert the name of the parent folder in the example below? I am trying to set the path of the new text file in the same folder as the file opened (a, I think). Sure this is as clear as mud!

    <pre>Dim fs, a, RS, temptext
    Dim peanut As String
    Dim coconut As String
    Dim STN As String ' Store Number
    Dim DTE As String 'File Date
    Dim getpeanut
    'Get FileName to open
    getpeanut = Application.GetOpenFilename("SDF Files (*.sdf), *.sdf")
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.opentextfile(getpeanut, 1, -2)
    'Establish new filename
    peanut = a.readline
    STN = Mid(peanut, 3, 5)
    DTE = Mid(peanut, 9, 6)
    'Create new text file
    Set RS = CreateObject("Scripting.filesystemobject")
    Set temptext = RS.createtextfile("C:Accounting 4.01ServerIncomingSDF Files" _
    & <font color=red>"Parent Name Here"</font color=red> & "" & STN & " " & DTE & ".txt", True)
    Set a = Nothing
    Set a = fs.opentextfile(getpeanut, 1, -2)
    Do While a.atendofstream <> True</pre>


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent Folder (Excel 2000)

    A specific example of the name of the textfile and the name of the new file and it's location (and how they can/should be derived) would help...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent Folder (Excel 2000)

    Hi Jan,

    The name of the opened file is always going to be Data01.sdf. It can be in various directories however. Essentially I am opening the Data01.sdf file, reading the first few characters in the file (the store number and date), and saving the new text file with the name of the store number and date. If I hard code the save location, this works fine. However, I would like to save the new text file in the same place as the opened file (Data01.sdf) via code due to the possibility of the user navigating to a different Data01.sdf file and saving a text file in a location which has no bearing on the Data01 file. Does this help or is it more confusing?

    The big challenge is that the cash register systems at 16 stores each create a file called Data01. This file has all of the information for the previous day. Much of the information is unnecessary. I would like to be able to open the file, read certain parts of it, and write the selected information to a new text file with a unique name which corresponds to the store number and date.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent Folder (Excel 2000)

    How about:

    <pre>Dim strPath As String
    strPath = Left(getpeanut, InStrRev(getpeanut, ""))
    </pre>

    Legare Coleman

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent Folder (Excel 2000)

    Thanks Legare, that works well!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent Folder (Excel 2000)

    Even though Michael states XL2000, please be aware that InstrRev is unavailable in XL97
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •