Truncated Extension-free Filename
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
Re: Truncated Extension-free Filename
This could be an issue
Code:
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
Re: Truncated Extension-free Filename
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
Re: Truncated Extension-free Filename
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
Re: Truncated Extension-free Filename
Quote:
Originally Posted by
techgnome
Tand I think lavolpe meant the last period, not the first...
-tg
:thumb:
Re: Truncated Extension-free Filename
Quote:
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?
Re: Truncated Extension-free Filename
Quote:
Originally Posted by
DataMiser
This could be an issue
Code:
s$ = Left(s$, Len(s$) - 4)
Omitted this line.
Quote:
Originally Posted by
techgnome
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.
Quote:
Originally Posted by
westconn1
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
Re: Truncated Extension-free Filename
Quote:
Filename$ = Application.GetOpenFilename("[ApplicationName]Output file(*.*), *.*")
change this to the specific extension required