Parent Folder (Excel 2000)

Topic

New Reply

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!

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" _
        & "Parent Name Here" & "" & STN & "  " & DTE & ".txt", True)
    Set a = Nothing
    Set a = fs.opentextfile(getpeanut, 1, -2)
    Do While a.atendofstream  True

Reply | Quote

Replies

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…

Reply | Quote

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.

Reply | Quote

How about:

Dim strPath As String
    strPath = Left(getpeanut, InStrRev(getpeanut, ""))

Reply | Quote

Thanks Legare, that works well!

Reply | Quote

Even though Michael states XL2000, please be aware that InstrRev is unavailable in XL97

Reply | Quote