Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using wildcards in workbook calls
On Apr 28, 3:51*pm, Bishop wrote:
Why can't I get the following code to work? *I want this macro to find an open workbook called "ExportedData---" (where the dashes could be be anything) and perform the following actions. *That's why I'm using the * wildcard. *For example, one instance of the workbook might be named "ExportedData[2]" *the next instance might be called "ExportedData[1]". *As you can see from my comments I've tried messing around with a few different methods but just can't make it work. *In this current setup I'm getting a "Compile Error: *Argument not Optional" and it highlights the word Workbooks in the With statement. Sub CatalystToTally() 'Dim wb As Range 'Dim ws As Range 'Set wb = Workbooks 'Set ws = Worksheets * * With Workbooks Like "ExportedData*" * * * * '.Rows(1).Delete Shift:=xlUp * * * * .Rows("1:1").Delete Shift:=xlUp * * * * .Columns(D).ColumnWidth = 13 * * * * .Columns(D).NumberFormat = "0" * * * * .Cells.Select * * * * .Selection.Copy * * * * .ThisWorkbook.Worksheets("Catalyst Dump").Paste * * End With End Sub Bishop, The program is throwing an error on the "Workbooks" line because, in your specific example, when you qualify the workbooks class, you then need to tell VBA what method you want to perform or property information you want to read/write. In your example, you are looking to compare the workbook NAME with the "ExportedData*" wild card comparison. Below is an example that should give you enough syntax to play around with. Also, be sure to evaluate your code, line-by-line (using F8 repeatedly or Debug | Step Into) to see how your programs are behaving. Best, Matthew Herbert Sub CatalystToTally() Dim Wkb As Workbook Dim Wks As Worksheet 'loop through the workbooks collection For Each Wkb In Workbooks 'test if Wkb's name is Like "ExportedData*" If Wkb.Name Like "ExportedData*" Then 'create a worksheet object to reference the ' appropriate worksheet in Wkb Set Wks = Wkb.ActiveSheet 'manipulate Wks With Wks .Rows(1).Delete .Columns("D").ColumnWidth = 13 .Columns("D").NumberFormat = "0" 'copy and paste Wks into the specified destination (i.e. ' the destination is the workbook in which the VBA code ' resides) .Cells.Copy ThisWorkbook.Worksheets("Catalyst Dump").Range ("A1") End With End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using wildcards in workbook calls | Excel Programming | |||
Add-In Calls Sub in Active Workbook | Excel Programming | |||
Wildcards in workbook name | Excel Programming | |||
Wildcards in other workbook cell refrence? | Excel Worksheet Functions | |||
Any automation calls gets hung when the Excel workbook has an active cell. | Excel Programming |