Truncated Extension-free Filename

[VBediturd]

Hello everyone,

Since this is my first post I must preface with some relevant information. I have some (minimal experience) with VBA, but I would like to become more fluent. Currently, I am working on a project that requires some manipulation of a previously generated macro. The function of the macro is to take temporary output files, extract the data and import into excel for analysis. The software that generates the output files had some minor changes made when it was upgraded from an older version and I am stuck on one issue-implementing the truncated and extension-free filename into my spreadsheet. Ideally, the solution should be compatible across older and newer versions of excel (v.8 - v.15), if possible.

My desired outcume is to:

• Extract Filename = C:\path\....\SampleX.out
• Truncate Filename = SampleX.out
• Remove File Extension = "SampleX"
• Insert Filename to spec. Cells = Cells(1, 2)
• "Cells(1, 2)" Contents to "Cells(myrow, 1)

Here is some of the relevant code that I have so far:

---

Private Sub CmdButton_Click()

Dim SampleX As String

Filename$ = Application.GetOpenFilename("[ApplicationName]Output file(*.*), *.*")
If Len(Filename$) <= 0 Then Exit Sub
If Filename$ = "False" Then Exit Sub
Filename$ = Cells(1, 5)
Open Filename$ For Input As #1

Line Input #1, strtemp$

Do While (Not EOF(1))
s$ = Right(strtemp$, 5)
If s$ = "*****" Then
Line Input #1, SampleX
......
Exit Do

End If
Line Input #1, strtemp$
Loop

Close #1

aaa% = InStrRev(Filename$, "\")
s$ = Right(Filename$, Len(Filename$) - aaa%)
s$ = Left(s$, Len(s$) - 4)

myrow = 31
Do While (Cells(myrow, 1) <> "")
myrow = myrow + 1
Loop

Cells(1, 2) = s$

Cells(myrow, 1) = SampleX
....
End Sub

---

Solutions and detailed explanations are highly appreciated!


Thanks,

Mr. T. Ferguson

[DataMiser]

This could be an issue

s$ = Left(s$, Len(s$) - 4)

It is possible that a file may have more or less than 3 characters as an extension or even no extension at all so just blindly trimming off the last 4 characters may not be a good idea.
Would be better to check for the . and if it exists trim it and what follows.

This really belongs in the Office section since it seems to be Excel VBA rather than VB

[LaVolpe]

And to piggy back on DataMiser's suggestion. Look for the last backslash first then the 1st decimal (.) after that, should you have a path like the following with no extension on the file name...

C:\Temp\2014.15.25\mySampleLog

[techgnome]

To be fair, to help, we'd need to know what you're getting vs what you're expecting.

and I think lavolpe meant the last period, not the first...

-tg

[LaVolpe]

Tand I think lavolpe meant the last period, not the first...

-tg

[westconn1]

Here is some of the relevant code that I have so far:

while some improvements could be made to the code, it looks like it should work
what problems are you having with it?
are you getting errors or wrong results?

[VBediturd]

This could be an issue

s$ = Left(s$, Len(s$) - 4)

Omitted this line.

To be fair, to help, we'd need to know what you're getting vs what you're expecting.

The code is having me manually select the file that I would like to have automatically selected. I'm expecting that the code will automatically choose the the most recently modified *.out file in the specified path.

while some improvements could be made to the code, it looks like it should work
what problems are you having with it?
are you getting errors or wrong results?

Most of the errors have been corrected. File name is going to the right place now in the array. The only issue now is that if I try to autoselect the most recently modified output file, I get the wrong values in my spreadsheet. Guessing this is the result of using the wrong function as there are several different file formats in the spec'd folder (same file name--different extension).

Sorry for the delayed response.

TF

[westconn1]

Filename$ = Application.GetOpenFilename("[ApplicationName]Output file(*.*), *.*")

change this to the specific extension required