Excel macros

Hello
I have made a macro and want it to be more general,so that I can apply
it with any .log text file delivered by a audio ripping program. I want
a 'file open' at the beginning and a 'save as' at the end.
The trouble is that the macro will only work on the original data,
which is not much use.
I have been using the import data option to bring in the data which is
fixed width and use it in a prepared template.

I know nothing about Visual Basic, I do know that is what I need to
make the macro more general.

I can post the text of the macro.

Sub Format1()
'
' Format1 Macro
' Macro recorded 23/10/2005 by Engles
'

'
Range("A2").Select
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;O:\BBCSO\BBCSO002.log", _
Destination:=Range("A2"))
.Name = "BBCSO002"
.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
ChDir "O:\BBCSO"
ActiveWorkbook.SaveAs Filename:="O:\BBCSO\BBCSO.xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

Thanks for your help

Mike Engles

Mike,

How about this

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


--

HTH

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


<mike.engles@btinternet.com> wrote in message
news:1130107538.766356.35920@o13g2000cwo.googlegroups.com...
> Hello
> I have made a macro and want it to be more general,so that I can apply
> it with any .log text file delivered by a audio ripping program. I want
> a 'file open' at the beginning and a 'save as' at the end.
> The trouble is that the macro will only work on the original data,
> which is not much use.
> I have been using the import data option to bring in the data which is
> fixed width and use it in a prepared template.
>

Hello

Thanks for your efforts, I have only the slightest inkling of what you
have written.

Anyway I opened Excel,unhid my personal file and then went into edit
macro mode.
I deleted the text of my macro and pasted in yours, but the following
text sections
became red and the new macro stops at the red bits, with compile error
syntax error message.


sFileName = Right(sFileName, Len(sFileName) -
InStrRev(sFileName, "\"))



ActiveWorkbook.SaveAs Filename:=Replace(sFile, ".log", "") & ".xls",
_
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False


Mike Engles

Mike,

Probably wrap-around intrduced by the newsgroup.

Try this revision

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


--

HTH

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


<mike.engles@btinternet.com> wrote in message
news:1130192499.034862.269410@g43g2000cwa.googlegroups.com...
> Hello
>
> Thanks for your efforts, I have only the slightest inkling of what you
> have written.
>
> Anyway I opened Excel,unhid my personal file and then went into edit
> macro mode.
> I deleted the text of my macro and pasted in yours, but the following
> text sections
> became red and the new macro stops at the red bits, with compile error
> syntax error message.
>
>
> sFileName = Right(sFileName, Len(sFileName) -
> InStrRev(sFileName, "\"))
>
>
>
> ActiveWorkbook.SaveAs Filename:=Replace(sFile, ".log", "") & ".xls",
> _
> FileFormat:=xlNormal, _
> Password:="", _
> WriteResPassword:="", _
> ReadOnlyRecommended:=False, _
> CreateBackup:=False
>
>
> Mike Engles
>

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