Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Hi all
I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Hello JJ(UK),
I'm not sure if this will help but give this a try.... In an Excel Spreadsheet, place the following code in the Workbook_Open() subroutine: Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Dim FileS As FileSearch Dim File As Variant On Error Resume Next Dim usedRng As Range ThisWorkbook.Worksheets(1).Activate DetermineUsedRange usedRng usedRng.Clear Set FileS = Application.FileSearch With FileS .NewSearch .FileName = "*.log" .LookIn = "\\Sever\log$" .SearchSubFolders = False .Execute End With For Each File In Application.FileSearch.FoundFiles ImportTextFile File, "," Next File Skip: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic What this code does is find auto-generated log files, clears the spreadsheet and then imports the data (comma delimited). Now you can change this to clear only a dynamic named range (ie. =OFFSET(Sheet1!$A$5,0,0,COUNTA(Sheet1!$A:$A)*2,10) ), so that your column headings do not get blown away. Also, you would change it to find the files on your server, directory with your file naming wildcard. This is very simplistic and can be augmented for a more robust solution. This should get you in the direction I think you are tying to go. HTH, Dean. "JJ (UK)" wrote: Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Get the script file to run Excel and open the workbook with your macro. The
macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the ..csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Nice and simple. Except for one thing. How do I pass the name of the .csv
file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a ..csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Surely there is a rule that you can codify. You have the problem however you
do it. An alternative has just occurred to me. Your Excel macro could do a GetOpenFilename to browse for the file. fileToOpen = oXLApp_ .GetOpenFilename("Text Files (*.csv), *.csv") If fileToOpen < False Then oXLApp.Workbooks.Open fileToOpen End If -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Nice and simple. Except for one thing. How do I pass the name of the .csv file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
I like that GetOpenFilename thang, although it's no good here since I want
the script to run unattended... JJ (UK) "Bob Phillips" wrote in message ... Surely there is a rule that you can codify. You have the problem however you do it. An alternative has just occurred to me. Your Excel macro could do a GetOpenFilename to browse for the file. fileToOpen = oXLApp_ .GetOpenFilename("Text Files (*.csv), *.csv") If fileToOpen < False Then oXLApp.Workbooks.Open fileToOpen End If -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Nice and simple. Except for one thing. How do I pass the name of the ..csv file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
So, as I said before, what are the rules to determine the filename?
-- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... I like that GetOpenFilename thang, although it's no good here since I want the script to run unattended... JJ (UK) "Bob Phillips" wrote in message ... Surely there is a rule that you can codify. You have the problem however you do it. An alternative has just occurred to me. Your Excel macro could do a GetOpenFilename to browse for the file. fileToOpen = oXLApp_ .GetOpenFilename("Text Files (*.csv), *.csv") If fileToOpen < False Then oXLApp.Workbooks.Open fileToOpen End If -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Nice and simple. Except for one thing. How do I pass the name of the .csv file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Oh, sorry, within the batch script it's something like
for /f %a in ('date /t') do set date=%a && set month=%b && set year=%c for /f %a in ('time /t') do set hour=%a && set year=%b then further down echo Server,Drive,SpaceIS_ScanResults_%year%%month%%da te%_%hour%.%minutes%.csv So the report will be appended with the date and time the script was started thus creating a unique name everytime. The alternative is to keep the .csv filename the same and get the Excel macro to SaveAs with the date and time that the macro was running which I guess will be close enough...I've got the code to do this somewhere... JJ (UK) "Bob Phillips" wrote in message ... So, as I said before, what are the rules to determine the filename? -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... I like that GetOpenFilename thang, although it's no good here since I want the script to run unattended... JJ (UK) "Bob Phillips" wrote in message ... Surely there is a rule that you can codify. You have the problem however you do it. An alternative has just occurred to me. Your Excel macro could do a GetOpenFilename to browse for the file. fileToOpen = oXLApp_ .GetOpenFilename("Text Files (*.csv), *.csv") If fileToOpen < False Then oXLApp.Workbooks.Open fileToOpen End If -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Nice and simple. Except for one thing. How do I pass the name of the .csv file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Sorry, don't know that code. But could you not plug that value into the part
of the script that opens the file? -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Oh, sorry, within the batch script it's something like for /f %a in ('date /t') do set date=%a && set month=%b && set year=%c for /f %a in ('time /t') do set hour=%a && set year=%b then further down echo Server,Drive,SpaceIS_ScanResults_%year%%month%%da te%_%hour%.%minutes%.csv So the report will be appended with the date and time the script was started thus creating a unique name everytime. The alternative is to keep the .csv filename the same and get the Excel macro to SaveAs with the date and time that the macro was running which I guess will be close enough...I've got the code to do this somewhere... JJ (UK) "Bob Phillips" wrote in message ... So, as I said before, what are the rules to determine the filename? -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... I like that GetOpenFilename thang, although it's no good here since I want the script to run unattended... JJ (UK) "Bob Phillips" wrote in message ... Surely there is a rule that you can codify. You have the problem however you do it. An alternative has just occurred to me. Your Excel macro could do a GetOpenFilename to browse for the file. fileToOpen = oXLApp_ .GetOpenFilename("Text Files (*.csv), *.csv") If fileToOpen < False Then oXLApp.Workbooks.Open fileToOpen End If -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Nice and simple. Except for one thing. How do I pass the name of the .csv file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Ah, that's what I'm trying to do.
I was hoping I'd be able to do something like this: Excel.exe IS_ScanResults_20041130_11.28.csv /m:"Macros.xls:FormatMailServerCapacityReport" So, run Excel, opening the .csv report and run the macro called FormatMailServerCapacityReport stored within macros.xls. JJ (UK) "Bob Phillips" wrote in message ... Sorry, don't know that code. But could you not plug that value into the part of the script that opens the file? -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Oh, sorry, within the batch script it's something like for /f %a in ('date /t') do set date=%a && set month=%b && set year=%c for /f %a in ('time /t') do set hour=%a && set year=%b then further down echo Server,Drive,SpaceIS_ScanResults_%year%%month%%da te%_%hour%.%minutes%.csv So the report will be appended with the date and time the script was started thus creating a unique name everytime. The alternative is to keep the .csv filename the same and get the Excel macro to SaveAs with the date and time that the macro was running which I guess will be close enough...I've got the code to do this somewhere... JJ (UK) "Bob Phillips" wrote in message ... So, as I said before, what are the rules to determine the filename? -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... I like that GetOpenFilename thang, although it's no good here since I want the script to run unattended... JJ (UK) "Bob Phillips" wrote in message ... Surely there is a rule that you can codify. You have the problem however you do it. An alternative has just occurred to me. Your Excel macro could do a GetOpenFilename to browse for the file. fileToOpen = oXLApp_ .GetOpenFilename("Text Files (*.csv), *.csv") If fileToOpen < False Then oXLApp.Workbooks.Open fileToOpen End If -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Nice and simple. Except for one thing. How do I pass the name of the .csv file to the macro bearing in mind the .csv filename changes each time the batch file is run... JJ (UK) "Bob Phillips" wrote in message ... Get the script file to run Excel and open the workbook with your macro. The macro can open the csv and so it's bit, closing Excel down at the end. Just make sure there is no screen interaction in the macro, or else it will need to be attended Dim oXLApp Set oXLApp = CreateObject("Excel.Application") oXLApp.Workbooks.Open "C:\dir\file.xls" -- HTH RP (remove nothere from the email address if mailing direct) "JJ (UK)" wrote in message ... Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a .csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the .csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Start Excel and run a macro on a given filename?
Dean
Sorry for not responding earlier. First time I read your post it gave me a headache, but I think I see what you mean now. It's in the melting pot, just as soon as I find a spare moment to give it a go. Thank you. :-) JJ (UK) "Dean Hinson" wrote in message ... Hello JJ(UK), I'm not sure if this will help but give this a try.... In an Excel Spreadsheet, place the following code in the Workbook_Open() subroutine: Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Dim FileS As FileSearch Dim File As Variant On Error Resume Next Dim usedRng As Range ThisWorkbook.Worksheets(1).Activate DetermineUsedRange usedRng usedRng.Clear Set FileS = Application.FileSearch With FileS .NewSearch .FileName = "*.log" .LookIn = "\\Sever\log$" .SearchSubFolders = False .Execute End With For Each File In Application.FileSearch.FoundFiles ImportTextFile File, "," Next File Skip: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic What this code does is find auto-generated log files, clears the spreadsheet and then imports the data (comma delimited). Now you can change this to clear only a dynamic named range (ie. =OFFSET(Sheet1!$A$5,0,0,COUNTA(Sheet1!$A:$A)*2,10) ), so that your column headings do not get blown away. Also, you would change it to find the files on your server, directory with your file naming wildcard. This is very simplistic and can be augmented for a more robust solution. This should get you in the direction I think you are tying to go. HTH, Dean. "JJ (UK)" wrote: Hi all I was hoping there were some command line switches that would allow this functionality but I can't find them. I've found instructions on automatically running a macro within an existing workbook by calling it Auto_Open but that doesn't quite match what I need. What we have is a batch script that runs every Monday and produces a ..csv file called IS_ScanResults_yyyymmdd_hh.mm.csv. As you can see the filename changes every time the script runs (such as IS_ScanResults_20041130_11.28.csv if the script ran today at 11:28 am). I've also written a VB macro that tidies up the raw .csv file in to a more presentable Excel spreadsheet, converting bytes to megabytes and sorting by one of the columns etc. etc. What I'd like to do is at the end of the batch file get it to open the ..csv file in Excel and run the macro (called 'FormatMailServerCapacityReport' and stored in macros.xls) on that .csv file. Is there a way of achieving this? Thanks in advance JJ (UK) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel macro to prompt for filename | Excel Discussion (Misc queries) | |||
Help? start macro automatic in excel | Excel Worksheet Functions | |||
How to save a vary filename by Macro in Excel? | Excel Programming | |||
Excel auto-start macro | Excel Programming | |||
Macro to Create PDF from excel with filename... | Excel Programming |