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