Solved: Code help from what I got

[lynnnow]

Hi,

I got a reference of exporting the word count and page count from Word to Excel. However, now there is a new problem.

The code I am using is as below (I've modified some part of it to suit my need, however there is one line of code that is throwing the rest of it off. I need a workaround or a solution to this)

[VBA]Option Explicit
Sub ExcelMacro()

Dim ObjExcel As Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim TTN As String
Dim FName As String

TTN = InputBox("Enter the TTN for posting the data to:", "...:::Lynx's Corner:::...")
FName = "ABC.xls"
Set ObjExcel = Excel.Application 'New ///This is the offending line. It starts a new instance of Excel which i don't want since the excel file to edit is already open.///
ObjExcel.Visible = True 'Add this line to make the Excel app visible
ObjExcel.Workbooks(FName).Activate
'Set Wkb = ObjExcel.Workbooks(FName).Activate
Wkb.Sheets("New Format").Activate
'Set WS = Wkb.Sheets("New Format")
Cells.Find(TTN).Activate
ActiveCell.Offset(0, 30).Value = ActiveDocument.BuiltInDocumentProperties(wdPropertyWords)
ActiveCell.Offset(0, 31).Value = ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
'Wkb.Close False ///I've disabled these lines since i don't want excel to quit and close the file.
'ObjExcel.Quit

'Set ObjExcel = Nothing
'Set Wkb = Nothing
'Set WS = Nothing

End Sub [/VBA]

Any help is appreciated.

Lynx.

[Jacob Hilderbrand]

You can use GetObject to get the open Excel application.

[VBA]Set ObjExcel = GetObject(, "Excel.Application")[/VBA]

[lynnnow]

Hi,

Thank you for that suggestion, however, it shows an error that the ActiveX component cannot create the object. What can be the next suggestion?

Lynx

[lynnnow]

Hi,

Ok. I forgot to put in the quotes. sorry about that. but the code running got stuck again at two lines below in
[VBA] ObjExcel.Workbooks(FName).Activate [/VBA]
it shows a Subscript Out of Range error now.

what do i do?

[Jacob Hilderbrand]

[VBA]FName = "ABC.xls"[/VBA]
Make sure that there is a workbook called "ABC.xls" open in Excel.

[lynnnow]

Hi,

i went back and read the help on GetObject and the modification i've done is this

[VBA] Dim ObjExcel as Object
.
.
.
'Now this line just checks the instance of Excel and whether
'the file that is open
Set ObjExcel = GetObject("ABC.xls")
[/VBA]

I've deactivated the lines after this till Cells.Find(TTN).Activate. My problem now lies on this line i.e. it shows an Object Failure error.

Since i've learned VBA off the forums and reading parts of the help files, I'm not very versed in solving the problem.

Any light on this subject will be appreciated.

Thanks.

[Jacob Hilderbrand]

If the file is not open the you will get an error. Try something like this:
[vba]
Dim IsOpen As Boolean

On Error Resume Next 'Ignore Errors
Set objexcel = GetObject("ABC.xls")
If objexcel Is Nothing Then
IsOpen = False
Else
IsOpen = True
End If
'Alternately you can replace the above If statement with the following line
'IsOpen = Not objexcel Is Nothing
On Error GoTo 0 'Reset Errors

If IsOpen = True Then
MsgBox "the file is open"
Else
MsgBox "the file is NOT open"
End If
[/vba]

[lynnnow]

Hi,

There seems to be some confusion on my problem.

I've got the file open in Excel, the file is referred to appropriately in Excel with the GetObject line.

This is what has become of the code, i.e. not including the above suggestion.

[VBA] Option Explicit
Sub ExcelMacro()

Dim ObjExcel As Object 'Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim TTN As String
Dim FName As String

TTN = InputBox("Enter the TTN for posting the data to:", "...:::Lynx's Corner:::...")
FName = "C:\Lincoln\Lincoln Transcriber's Log.xls"
Set ObjExcel = GetObject(FName) 'New
'ObjExcel.Visible = True 'Add this line to make the Excel app visible
'ObjExcel.Workbooks(FName).Activate
'Set Wkb = ObjExcel.Workbooks(FName).Activate
'Wkb.Sheets("New Format").Activate
'Set WS = Wkb.Sheets("New Format")
'Search
Cells.Find(TTN).Activate 'This is the offending line now
'.Activate
ActiveCell.Offset(0, 30).Value = ActiveDocument.BuiltInDocumentProperties(wdPropertyWords)
ActiveCell.Offset(0, 31).Value = ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
'Wkb.Close False
'ObjExcel.Quit

'Set ObjExcel = Nothing
'Set Wkb = Nothing
'Set WS = Nothing

End Sub [/VBA]

As you can see from the code, by disabling the .Activate line, the problem is now the Cells.Find(TTN).Activate line and it continues through the rest of the ActiveCell.Offset.... parts.

It shows that it is an ActiveX failure in the _Global...

[Jacob Hilderbrand]

Ok, instead of activating the cell we can do something like this:
[vba]
Dim Cel As Range

Set Cel = Cells.Find(TNN)

Cel.OffSet(0,30).Value = ...
[/vba]

[lynnnow]

Hi,

Tried your suggestion but this is what i got:

On the Set Cel = Cells.Find(TTN) line i got a Method 'Cells' of object '_Global' failed. A runtime 1004 error. and i can't view the help file it gives me an error of "The topic does not exist. Contact your application vendor for an updated help file. (129)"

For this error, i had researched a site and followed the steps it provided, however, it has not helped.

[Jacob Hilderbrand]

Try this:

[VBA]Set Cel = WS.Cells.Find(TTN)[/VBA]

[lynnnow]

Hi,

Tried even that, didn't work. it shows a object variable or with block not set error.

i've solved this kind of problem in excel today the code for that was something i worked out with the VBA help file, that code went like this:

[VBA] With Worksheets(1).Range("A1:IV65536")
FindDate = UserForm2.ComboBox2.Value & " 1, " & Str(Year(Date))
Set DateFind = .Find(CDate(FindDate))
If Not DateFind Is Nothing Then
Cells.Find(CDate(FindDate)).Activate
Else
FindDate = UserForm2.ComboBox2.Value & " 2, " & Str(Year(Date))
Set DateFind = .Find(CDate(FindDate))
If Not DateFind Is Nothing Then
Cells.Find(CDate(FindDate)).Activate
Else
FindDate = UserForm2.ComboBox2.Value & " 3, " & Str(Year(Date))
Set DateFind = .Find(CDate(FindDate))
If Not DateFind Is Nothing Then
Cells.Find(CDate(FindDate)).Activate
Else
MsgBox "The first three days for the month were searched for and not found", vbCritical, "Lynx's Corner"
Unload Me
Workbooks("Transcriber's TM Sheet.xls").Close False
End If
End If
End If
End With [/VBA]

I tried using this principle here, but it shows the above mentioned error msg.

the one it tried in Word looks like this:

[VBA] With WS.Range("A1:IV65536")
Set CellToFind = Cells.Find(TTN)
If Not CellToFind Is Nothing Then
MsgBox "Please check the input", vbCritical, "Lynx's Corner"
Else
Cells.Find(TTN).Activate
End If
End With[/VBA]

I'm really coming to a blank with this problem.

[Jacob Hilderbrand]

Can you post an attachment of the workbook?

[lynnnow]

Hi,

I've given a sample of the file, however, this is the data to where it will copy the word count and page count to the extreme right portion of the table.

Hope something comes out of this.

[Jacob Hilderbrand]

Can you also post the Word document with the code you are using so I can try and debug it?

[lynnnow]

Hi,

The word document will be a different one all the time, so you can use any document to pull the word count and page count from.

the entire code as i have it is as below:

[VBA]Option Explicit
Sub ExcelMacro()

Dim ObjExcel As Object 'Excel.Application
'Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim TTN As String
Dim FName As String
Dim CellToFind As Range

TTN = InputBox("Enter the TTN for posting the data to:", "...:::Lynx's Corner:::...")
FName = "C:\Lincoln\Lincoln Transcriber's Log.xls"
Set ObjExcel = GetObject(FName) 'New
'ObjExcel.Visible = True 'Add this line to make the Excel app visible
'ObjExcel.Workbooks(FName).Activate
'Set Wkb = ObjExcel.Workbooks(FName).Activate
'Wkb.Sheets("New Format").Activate
'Set WS = Wkb.Sheets("New Format")
'Set CellToFind = WS.Cells.Find(TTN).Activate
With .Range("A1:IV65536")
Set CellToFind = WS.Cells.Find(TTN)
If Not CellToFind Is Nothing Then
MsgBox "Please check the input", vbCritical, "Lynx's Corner"
Else
WS.Cells.Find(TTN).Activate
End If
End With
ActiveCell.Offset(0, 30).Value = ActiveDocument.BuiltInDocumentProperties(wdPropertyWords)
ActiveCell.Offset(0, 31).Value = ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
'Wkb.Close False
'ObjExcel.Quit

'Set ObjExcel = Nothing
'Set Wkb = Nothing
'Set WS = Nothing

End Sub
[/VBA]

I've kept the disable parts also, so i can just go back and re-enable them if there is a need.

Thanks for taking so much interest in this problem

[lynnnow]

I've put the code in the normal.dot file.

[Jacob Hilderbrand]

Ok, give this code a try.
[vba]
Option Explicit

Sub ExcelMacro()

Dim ObjExcel As Excel.Application
Dim Wkb As Excel.Workbook
Dim WS As Excel.Worksheet
Dim TTN As String
Dim FName As String
Dim CellToFind As Excel.Range

TTN = InputBox("Enter the TTN for posting the data to:", _
"...:::Lynx's Corner:::...")

FName = "Transcriber's Log.xls"
Set ObjExcel = GetObject(, "Excel.Application") 'New
ObjExcel.Visible = True 'Add this line to make the Excel app visible
Set Wkb = ObjExcel.Workbooks(FName)
Set WS = Wkb.Sheets("New Format")
Set CellToFind = WS.Cells.Find(What:=TTN, LookIn:=xlValues)
If CellToFind Is Nothing Then
MsgBox "Please check the input", vbCritical, "Lynx's Corner"
Else
CellToFind.Offset(0, 30).Value = _
ActiveDocument.BuiltInDocumentProperties(wdPropertyWords)

CellToFind.Offset(0, 31).Value = _
ActiveDocument.BuiltInDocumentProperties(wdPropertyPages)
End If

Wkb.Close True 'True to Save, False to not Save
ObjExcel.Quit

Set ObjExcel = Nothing
Set Wkb = Nothing
Set WS = Nothing

End Sub[/vba]

[lynnnow]

Hi,

Did this code run successfully on your PC, coz on mine it just shows Subscript out of range on the Set Wkb = ObjExcel.Workbooks(FName) line

Just FYI, i'm using Excel 97 on Win 2k.

Thanks for being so much of a help when i'm ripping my hair out.

[lynnnow]

Hi,

Ok. sorry for the above msg. I found the rat. The rat was that the file name was not getting properly in. the file name is actually, "Lincoln Transcriber's Log" and not "Transcriber's Log". so i added the Lincoln and the code worked superbly. Thanks for all the help