Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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:\B BCSO\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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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) wrote in message ups.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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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) wrote in message oups.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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) wrote in message oups.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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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 wrote in message oups.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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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) wrote in message oups.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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macros
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
convert lotus 123w macros to excel macros | Excel Programming | |||
Making excel macros run Word macros | Excel Programming |