Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run formatting macro if filename has a particular substring in it
Hi, I'm relatively new to VB/Excel/etc... trying to figure this out.
I have a spreadsheet that is created by a LabView program. It includes no formatting. I'd like to automatically format the spreadsheet when it is opened... but I'd only like the formatting part of the macro to run if the filename has a particular substring in it. ex. filename: "1-27-04 Station1 Potassium.xls" the macro would run and would format b/c in the path, the substring "Station1" is there. Is the best way to do this to set my macro as Auto_Open, then add a condition at the beginning that parses the filename and runs the formatting only if that substring is there? Does the Auto_Open macro run whenever any Excel file is opened on that PC? Any examples of how to search for a substring in the filename? Is this even do-able? If it isn't I will have to succumb to the people that make LabView and buy their $500 plug-in that does ActiveX formatting and report-making. I'd rather do it simple and cheap if possible. Thanks a million in advance... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run formatting macro if filename has a particular substring in it
If LabView is going to create the file, at what point will code be placed in
the file? if Instr(1,activeworkbook.Name,"station1",vbTextCompa re) 0 then ' format the sheet End If See Chip Pearson's page on Events http://www.cpearson.com/excel/events.htm on writing code with code http://www.cpearson.com/excel/vbe.htm on working with LabView http://www.cpearson.com/excel/Im_Jus...About_That.htm -- Regards, Tom Ogilvy "stag5353" wrote in message ups.com... Hi, I'm relatively new to VB/Excel/etc... trying to figure this out. I have a spreadsheet that is created by a LabView program. It includes no formatting. I'd like to automatically format the spreadsheet when it is opened... but I'd only like the formatting part of the macro to run if the filename has a particular substring in it. ex. filename: "1-27-04 Station1 Potassium.xls" the macro would run and would format b/c in the path, the substring "Station1" is there. Is the best way to do this to set my macro as Auto_Open, then add a condition at the beginning that parses the filename and runs the formatting only if that substring is there? Does the Auto_Open macro run whenever any Excel file is opened on that PC? Any examples of how to search for a substring in the filename? Is this even do-able? If it isn't I will have to succumb to the people that make LabView and buy their $500 plug-in that does ActiveX formatting and report-making. I'd rather do it simple and cheap if possible. Thanks a million in advance... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run formatting macro if filename has a particular substring in it
"stag5353" wrote in message ups.com... Hi, I'm relatively new to VB/Excel/etc... trying to figure this out. I have a spreadsheet that is created by a LabView program. It includes no formatting. I'd like to automatically format the spreadsheet when it is opened... but I'd only like the formatting part of the macro to run if the filename has a particular substring in it. ex. filename: "1-27-04 Station1 Potassium.xls" the macro would run and would format b/c in the path, the substring "Station1" is there. Is the best way to do this to set my macro as Auto_Open, then add a condition at the beginning that parses the filename and runs the formatting only if that substring is there? Does the Auto_Open macro run whenever any Excel file is opened on that PC? Probably, but you would want to delete the code onec it has run (so as to avoid formatting again), or have a test based on some objcet to bypass it. Code to remove Auto_Open. #If Not EarlyBound Then Const vbext_pk_Proc = 0 #End If '---------------------------------------------------------------- Sub DeleteProcedure() '---------------------------------------------------------------- Dim oCodeModule As Object Dim iStart As Long Dim cLines As Long Set oCodeModule = ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule With oCodeModule On Error GoTo dp_err: iStart = .ProcStartLine("Auto_Open", vbext_pk_Proc) cLines = .ProcCountLines("Auto_Open", vbext_pk_Proc) .DeleteLines iStart, cLines On Error GoTo 0 Exit Sub End With dp_err: If Err.Number = 35 Then MsgBox "Procedure does not exist" End If End Sub Any examples of how to search for a substring in the filename? If Instr(1, activeworkbook.name,"STations" Then '... do your stuff End If Is this even do-able? If it isn't I will have to succumb to the people that make LabView and buy their $500 plug-in that does ActiveX formatting and report-making. I'd rather do it simple and cheap if possible. Yes, it is do-able as shown. $250 please! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Filename in Macro | Excel Discussion (Misc queries) | |||
macro reference changes with filename | Excel Discussion (Misc queries) | |||
filename in macro | Excel Discussion (Misc queries) | |||
Can excel macro access SUBstring individual 'char' level? If so how? | Excel Discussion (Misc queries) | |||
MACRO TO FIND SUBSTRING OR SUBTEXT ! | Excel Programming |