Excel macros

Hello

Wow thanks again, some progress.
The last bit is still red.
So I deleted that and ran the macro.
I do get a file open dialogue, so I can browse for a file and the
macro runs.
At the end the VB window opens with no text, which I close and see the
completed macro and then
I can do a file file save.

I have to say I am very impressed at how anyone could learn this stuff.
I did some Fortran many many years ago, but was singularly useless at
it.

My day job is a audio engineer. I need to analyse these error logs from
a audio ripping programme.

Mike Engles

Unfortunately,. I don't see it until after it posts. Revision #2

Sub Format1()
Dim sFile
Dim sFileName As String
sFile = Application.GetOpenFilename("Log Files (*.log), *.log")
If sFile <> False Then
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & sFile, _
Destination:=Range("A2"))
sFileName = Replace(sFile, ".log", "")
sFileName = Right(sFileName, Len(sFileName) - _
InStrRev(sFileName, "\"))
.Name = sFileName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 9)
.TextFileFixedColumnWidths = Array(20, 15, 36, 19)
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.SaveAs _
Filename:=Replace(sFile, ".log", "") & ".xls",_
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
End Sub

try it and report back.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<mike.engles@btinternet.com> wrote in message
news:1130196567.026632.310250@g43g2000cwa.googlegroups.com...
> Hello
>
> Wow thanks again, some progress.
> The last bit is still red.
> So I deleted that and ran the macro.
> I do get a file open dialogue, so I can browse for a file and the
> macro runs.
> At the end the VB window opens with no text, which I close and see the
> completed macro and then
> I can do a file file save.
>
> I have to say I am very impressed at how anyone could learn this stuff.
> I did some Fortran many many years ago, but was singularly useless at
> it.
>
> My day job is a audio engineer. I need to analyse these error logs from
> a audio ripping programme.
>
> Mike Engles
>

It might help your learning curve to realize that when you see the red
induced by word wrap that you can simply go the end of the line above and
use backspace key until the lower line moves up. OR, use the continuation
with a dash and an underscore

aaaaaaaaaaaa _
bbbbbbbbb

--
Don Guillett
SalesAid Software
donaldb@281.com
<mike.engles@btinternet.com> wrote in message
news:1130196567.026632.310250@g43g2000cwa.googlegroups.com...
> Hello
>
> Wow thanks again, some progress.
> The last bit is still red.
> So I deleted that and ran the macro.
> I do get a file open dialogue, so I can browse for a file and the
> macro runs.
> At the end the VB window opens with no text, which I close and see the
> completed macro and then
> I can do a file file save.
>
> I have to say I am very impressed at how anyone could learn this stuff.
> I did some Fortran many many years ago, but was singularly useless at
> it.
>
> My day job is a audio engineer. I need to analyse these error logs from
> a audio ripping programme.
>
> Mike Engles
>

Hello Bob and Don

Bob,Sorry no more luck with the last bit.
Would it help if I post a screen grab of the VB window?

http://www.btinternet.com/~mike.engles/mike/VBpage.jpg

Don, I have tried that, but then the debugger seems to take exception
to the : , and _
in the last statements.

http://www.btinternet.com/~mike.engles/mike/VBpage2.jpg

Mike Engles

Mike,

This line in the red section is not right

Filename:-Replace(sFile, ".log", "") & ".xls",


it should be

Filename:=Replace(sFile, ".log", "") & ".xls", _

In the second you have rolled SaveAs and FileName together, they should be
space separated, but make sure you maintain the continuation characters.

--

HTH

RP
(remove nothere from the email address if mailing direct)


<mike.engles@btinternet.com> wrote in message
news:1130264598.707732.268390@g14g2000cwa.googlegroups.com...
> Hello Bob and Don
>
> Bob,Sorry no more luck with the last bit.
> Would it help if I post a screen grab of the VB window?
>
> http://www.btinternet.com/~mike.engles/mike/VBpage.jpg
>
> Don, I have tried that, but then the debugger seems to take exception
> to the : , and _
> in the last statements.
>
> http://www.btinternet.com/~mike.engles/mike/VBpage2.jpg
>
> Mike Engles
>

Hello BOB

What a difference a small space before a _ makes!, not a very good song
lyric, but

Hurrah it works.

I can now rip a whole lot of audio DATs and convert the error logs into
an Excel spreadsheet.
This is what it looks like.

http://www.btinternet.com/~mike.engles/mike/Errors.jpg

I need to tweak the template a bit.

As you can see I know almost nothing about Excel or spreadsheets.
Do you know of a easy book, I can use?


Thanks very much again.

Mike Engles