Bishop, If you are sure there is only one workbook by that name and want to
activate the workbook with a keyword paste the below function and call from
code..to activate..Try and feedback..
Call ActivateBookwithKeyword ("C&A PF")
Sub ActivateBookwithKeyword(strSearch As String)
For intTemp = 1 To Workbooks.Count
If InStr(1, Workbooks(intTemp).Name, strSearch, 1) <> 0 Then
Workbooks(intTemp).Activate
Exit Sub
End If
Next
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria
"Bishop" wrote:
> The below code creates a toolbar, adds a button to that toolbar and assigns a
> sub to that button. When I name a specific workbook, say ("Bothell C&A PF
> May wk 1 bm"), it works fine when I run it from within VBE. But when I try
> to use a wild card, say ("*C&A PF*"), I get a run-time '9' error. Also, if I
> try to use the actual button from a spreadsheet it says it can't find X
> spreadsheet and then lists the name. But the name it lists is not even a
> spreadsheet I have open or have used in a long time. I'm using 2007 so the
> button is in Add-Ins. I would like to make this work from any spreadsheet.
>
> Sub CatalystDumpToolBar()
>
> Dim CDToolBar As CommandBar
>
> Set CDToolBar = CommandBars.Add(temporary:=True)
> With CDToolBar
> .Name = "CDToolBar"
> .Position = msoBarTop
> .Visible = True
> End With
> End Sub
>
> Sub CatalystToTally()
>
> Dim wb As Workbook
> Dim ws As Worksheet
> Dim CDLastRow As Long 'Catalyst Dump
> Dim EDLastRow As Long 'Exported Data
>
> 'This doesn't work
> Workbooks("*C&A PF*.xls").Activate
> 'This does work... how can I use wildcards for this?
> Workbooks("Bothell C&A PF May 09 wk1 bm.xls").Activate
> CDLastRow = Workbooks("*C&A PF*.xlsm").Worksheets _
> ("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
> Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13
>
> For Each wb In Workbooks
> 'Test to see if wb's name is like "ExportedData*"
> If wb.Name Like "ExportedData*" Then
> 'Create a worksheet object to reference the appropriate
> 'worksheet in the wb
> Set ws = wb.ActiveSheet
>
> With ws
> .Rows("1:1").Delete Shift:=xlUp
> EDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
> .Columns("D").ColumnWidth = 13
> .Columns("D").NumberFormat = "0"
> .Rows("1:" & EDLastRow).Copy ThisWorkbook.Worksheets _
> ("Catalyst Dump").Rows(CDLastRow + 1)
> End With
> wb.Close savechanges:=False
> End If
> Next
> End Sub
>
> Sub AddCustomControl()
>
> Dim CBar As CommandBar
> Dim CTTally As CommandBarControl
> Dim PFNum As CommandBarControl
>
> Set CBar = CommandBars("CDToolBar")
> Set CTTally = CBar.Controls.Add(Type:=msoControlButton)
> Set PFNum = CBar.Controls.Add(Type:=msoControlButton)
>
> With CTTally
> .FaceId = 1763
> .OnAction = "CatalystToTally"
> End With
>
> With PFNum
> .FaceId = 643
> .OnAction = "PFNumber"
> End With
>
> CBar.Visible = True
>
> End Sub
|
Similar Threads
