-
Nice! Thanks! I haven't changed much except to start each column on A2, B2, ect. and when I copied the loop to add new styles I got rid of the open excel comment and had to add a l = 0 to reset the row count.
Here is the code in all it's final glory! Named frosty_geek in honor of the creators
I only copied in 3 styles to keep it short, there are 17 styles in total.
One last question, How would I open an existing excel sheet on a mac? I'm used to the C: addresses from Windows.
For example I have this spreadsheet in Mac Harddrive > Users > Me > Documents > Work > 2012-05 Scripts
Thank you so much for all the help and tips! I'm sure to be back, and hopefully a little more educated!
[VBA]Sub frosty_geek()
'
' frosty_geek Macro
'
'
Dim oExcel As Object
Dim oBook As Object
Dim oRng As Object
Dim para As Paragraph
Dim strStyle As String
Dim l As Long
' use existing instance of Excel if possible
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set oExcel = CreateObject("Excel.Application")
End If
' should use proper error handling here
On Error GoTo 0
Set oBook = oExcel.Workbooks.Add
Set oRng = oBook.Sheets(1).Range("A2")
'set up the search
Dim rngSearch As Range
Set rngSearch = ActiveDocument.Content
With rngSearch.Find
.Style = "Activity Footage"
.Wrap = wdFindStop
.Format = True
'now execute until you don't find it... starting at the top of the document and stopping at the end
Do Until .Execute = False
oRng.Offset(l, 0).Formula = rngSearch.Text
l = l + 1
Loop
End With
' should use proper error handling here
On Error GoTo 0
Set oRng = oBook.Sheets(1).Range("B2")
l = 0
'set up the search
Set rngSearch = ActiveDocument.Content
With rngSearch.Find
.Style = "New Word"
.Wrap = wdFindStop
.Format = True
'now execute until you don't find it... starting at the top of the document and stopping at the end
Do Until .Execute = False
oRng.Offset(l, 0).Formula = rngSearch.Text
l = l + 1
Loop
End With
' should use proper error handling here
On Error GoTo 0
Set oRng = oBook.Sheets(1).Range("C2")
l = 0
'set up the search
Set rngSearch = ActiveDocument.Content
With rngSearch.Find
.Style = "Title Graphics"
.Wrap = wdFindStop
.Format = True
'now execute until you don't find it... starting at the top of the document and stopping at the end
Do Until .Execute = False
oRng.Offset(l, 0).Formula = rngSearch.Text
l = l + 1
Loop
End With
ExitHere:
On Error Resume Next
oExcel.Visible = True
oExcel.Activate
Set oRng = Nothing
Set oBook = Nothing
Set oExcel = Nothing
End Sub[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules