Run formatting macro if filename has a particular substring in it

[stag5353]

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...

 

[Tom Ogilvy]

If LabView is going to create the file, at what point will code be placed in
the file?

if Instr(1,activeworkbook.Name,"station1",vbTextCompare) > 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_Just_Joking_About_That.htm

 

[Bob Phillips]

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").CodeModule
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!

 

[Guest]

Hi,
If there is enought consistancy to the reports created by LabView, you might
try turning on the macro recorder as you format the next report. The code
created can be copied out and put in another file and saved into your
personal.xls. Once it is in the personal.xls, it is avialble all of the time.
You wold have to run the Macro, as it would not be set up to run
automatically, but macros run quickly. What you have posted here, does not
really give us enough detail to figure out how you want things formatted. If
you have a file and want to send a copy of the formated, with an unformated
(Two sheets), I will take a look at it. e-mail: (e-mail address removed)

Thanks,