Help in VBA programming
 

Hi ,
I am facing a major problem for my task.In this task
what we do is, export the comments entered in a word
document to an excel sheet.
The problem is as follows:

Before exporting the Word Comments to an excel sheet,it
has to check for the builtindocument properties,so that
only excel sheets with the particular title property are
selected.
i tried checking for the builtindocument property
(this is a macro in word which should get the excel sheet
builtindocument property) using the following code:

Private Function IsValidxls(xlBook As Object, Optional
sTemplateID As String) As Boolean

Dim bValid As Boolean
Dim sTemplateID As String

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.ActiveWorkbook
Set xlSheet = ActiveSheet

On Error GoTo ErrorHandler
sTemplateID = xlbook.BuiltinDocumentProperties
("keywords").Value
'// Check whether the selected excel sheet is a valid
template
If sTemplateID = MPP_GIP_TEMPLATE_ID Then
bValid = True
Else
'// This required.
'// Template ID will be retured from the function
when it is not matching
bValid = False
End If

ExitRoutine:
IsValidxls = bValid
Exit Function

ErrorHandler:
bValid = False
Resume ExitRoutine
End Function

when i run my program using this function it give an error
message "Object Variable or With block variable not set"

could anyone help me out regarding this.

Thanks & Regards,
Padmaja

Help in VBA programming
 

You don't say which line of code generated the error.
However, I can see from your code that yuo instantiate
excel OK, then you try to assign a workbook to xlBook.
You don't have a book open !

Set xlApp = CreateObject("Excel.Application")


Set xlBook = xlApp.Workbooks.Open(WorkBook_Path_Name)

OR if you use a new book using a template, use this
Set xlBook = xlApp.Workbooks.Add(template_Path_Name)

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi ,
I am facing a major problem for my task.In this
task
what we do is, export the comments entered in a word
document to an excel sheet.
The problem is as follows:

Before exporting the Word Comments to an excel sheet,it
has to check for the builtindocument properties,so that
only excel sheets with the particular title property are
selected.
i tried checking for the builtindocument property
(this is a macro in word which should get the excel
sheet
builtindocument property) using the following code:

Private Function IsValidxls(xlBook As Object, Optional
sTemplateID As String) As Boolean

Dim bValid As Boolean
Dim sTemplateID As String

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.ActiveWorkbook
Set xlSheet = ActiveSheet

On Error GoTo ErrorHandler
sTemplateID = xlbook.BuiltinDocumentProperties
("keywords").Value
'// Check whether the selected excel sheet is a valid
template
If sTemplateID = MPP_GIP_TEMPLATE_ID Then
bValid = True
Else
'// This required.
'// Template ID will be retured from the
function
when it is not matching
bValid = False
End If

ExitRoutine:
IsValidxls = bValid
Exit Function

ErrorHandler:
bValid = False
Resume ExitRoutine
End Function

when i run my program using this function it give an
error
message "Object Variable or With block variable not set"

could anyone help me out regarding this.

Thanks & Regards,
Padmaja
.

Help in VBA programming
 

Hi,
I have used the following
Set xlBook = xlApp.Workbooks.Open(WorkBook_Path_Name)
and its working.

Thank You Very Much

Thanks & Regards,
Padmaja

-----Original Message-----
You don't say which line of code generated the error.
However, I can see from your code that yuo instantiate
excel OK, then you try to assign a workbook to xlBook.
You don't have a book open !

Set xlApp = CreateObject("Excel.Application")


Set xlBook = xlApp.Workbooks.Open(WorkBook_Path_Name)

OR if you use a new book using a template, use this
Set xlBook = xlApp.Workbooks.Add(template_Path_Name)

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hi ,
I am facing a major problem for my task.In this
task
what we do is, export the comments entered in a word
document to an excel sheet.
The problem is as follows:

Before exporting the Word Comments to an excel sheet,it
has to check for the builtindocument properties,so that
only excel sheets with the particular title property are
selected.
i tried checking for the builtindocument property
(this is a macro in word which should get the excel
sheet
builtindocument property) using the following code:

Private Function IsValidxls(xlBook As Object, Optional
sTemplateID As String) As Boolean

Dim bValid As Boolean
Dim sTemplateID As String

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.ActiveWorkbook
Set xlSheet = ActiveSheet

On Error GoTo ErrorHandler
sTemplateID = xlbook.BuiltinDocumentProperties
("keywords").Value
'// Check whether the selected excel sheet is a valid
template
If sTemplateID = MPP_GIP_TEMPLATE_ID Then
bValid = True
Else
'// This required.
'// Template ID will be retured from the
function
when it is not matching
bValid = False
End If

ExitRoutine:
IsValidxls = bValid
Exit Function

ErrorHandler:
bValid = False
Resume ExitRoutine
End Function

when i run my program using this function it give an
error
message "Object Variable or With block variable not set"

could anyone help me out regarding this.

Thanks & Regards,
Padmaja
.

.