Controling Excel using Word VBA
 

Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Z,

Set a reference to Excel from within your Word project (in the VBE) and use code like this, below.
You really didn't explain a lot of the particulars, but this should get you going.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlCell As Excel.Range
Dim myValue As String 'Redim to match your user-entered data type

myValue = "Find me" 'Get this from your user's entered value in the combobox

Set xlApp = CreateObject("excel.application")

xlApp.Visible = True ' optional
Set xlBook = xlApp.Workbooks.Open("C:\DeleteMe.xls") ' change to actual file name
Set xlSht = xlBook.ActiveSheet
Set xlCell = xlSht.Range("A:A").Find(myValue) ' set search range

If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp)(2).Value = myValue
MsgBox "Added " & myValue & " to cell " & _
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp).Address(False, False, xlA1, True)
xlBook.Save
xlBook.Close
Else
MsgBox "Found " & myValue & " in cell " & xlCell.Address(False, False, xlA1, True)
End If

xlApp.Quit
Set xlApp = Nothing
End Sub



"Zoltar54" wrote in message
...
Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Bernie,

Thnaks for the help so far, perhaps I could ask a few more questions?

I have tried the code that you provided and it partially worked. As you
mentioned I didn't provide enough particulars. What else can I provide to
clrify the question?

Z

"Bernie Deitrick" wrote:

Z,

Set a reference to Excel from within your Word project (in the VBE) and use code like this, below.
You really didn't explain a lot of the particulars, but this should get you going.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlCell As Excel.Range
Dim myValue As String 'Redim to match your user-entered data type

myValue = "Find me" 'Get this from your user's entered value in the combobox

Set xlApp = CreateObject("excel.application")

xlApp.Visible = True ' optional
Set xlBook = xlApp.Workbooks.Open("C:\DeleteMe.xls") ' change to actual file name
Set xlSht = xlBook.ActiveSheet
Set xlCell = xlSht.Range("A:A").Find(myValue) ' set search range

If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp)(2).Value = myValue
MsgBox "Added " & myValue & " to cell " & _
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp).Address(False, False, xlA1, True)
xlBook.Save
xlBook.Close
Else
MsgBox "Found " & myValue & " in cell " & xlCell.Address(False, False, xlA1, True)
End If

xlApp.Quit
Set xlApp = Nothing
End Sub



"Zoltar54" wrote in message
...
Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Z,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes

How many? What naming scheme? What are they returning? How are they related to your database?

which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box,
the program searches the recordset and if the
entry does not exist to add it automatically.

Based on any value (combo-box), or a key value (primatry combo-box)?

Add it to the end of the database? Where is the key value (Sheet name and column) and what data do
you want to write to the database?

HTH,
Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

Thnaks for the help so far, perhaps I could ask a few more questions?

I have tried the code that you provided and it partially worked. As you
mentioned I didn't provide enough particulars. What else can I provide to
clrify the question?

Z

"Bernie Deitrick" wrote:

Z,

Set a reference to Excel from within your Word project (in the VBE) and use code like this,
below.
You really didn't explain a lot of the particulars, but this should get you going.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlCell As Excel.Range
Dim myValue As String 'Redim to match your user-entered data type

myValue = "Find me" 'Get this from your user's entered value in the combobox

Set xlApp = CreateObject("excel.application")

xlApp.Visible = True ' optional
Set xlBook = xlApp.Workbooks.Open("C:\DeleteMe.xls") ' change to actual file name
Set xlSht = xlBook.ActiveSheet
Set xlCell = xlSht.Range("A:A").Find(myValue) ' set search range

If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp)(2).Value = myValue
MsgBox "Added " & myValue & " to cell " & _
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp).Address(False, False, xlA1, True)
xlBook.Save
xlBook.Close
Else
MsgBox "Found " & myValue & " in cell " & xlCell.Address(False, False, xlA1, True)
End If

xlApp.Quit
Set xlApp = Nothing
End Sub



"Zoltar54" wrote in message
...
Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Bernie,

I'm not sure if I follow you. To start, I have two combo boxes that I need
to have update two seperate recordsets. The data consists of types of
facilities ie warehouse, storefront, 3rd part vendor etc. Each combobox is
loaded from a defined range in one excel workbook and are on the same sheet.
As it stands now, there are some names listed in the recordsets however I
need the source data (recordset) to be updated if and when a user enters a
term that is not already on the list. For example warehouse is on the lost
whereas Rail depot is not (and may need to be depending on the user
preference).

Does this clarify my needs further or did I make it worse.

Z



"Bernie Deitrick" wrote:

Z,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes

How many? What naming scheme? What are they returning? How are they related to your database?

which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box,
the program searches the recordset and if the
entry does not exist to add it automatically.

Based on any value (combo-box), or a key value (primatry combo-box)?

Add it to the end of the database? Where is the key value (Sheet name and column) and what data do
you want to write to the database?

HTH,
Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

Thnaks for the help so far, perhaps I could ask a few more questions?

I have tried the code that you provided and it partially worked. As you
mentioned I didn't provide enough particulars. What else can I provide to
clrify the question?

Z

"Bernie Deitrick" wrote:

Z,

Set a reference to Excel from within your Word project (in the VBE) and use code like this,
below.
You really didn't explain a lot of the particulars, but this should get you going.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlCell As Excel.Range
Dim myValue As String 'Redim to match your user-entered data type

myValue = "Find me" 'Get this from your user's entered value in the combobox

Set xlApp = CreateObject("excel.application")

xlApp.Visible = True ' optional
Set xlBook = xlApp.Workbooks.Open("C:\DeleteMe.xls") ' change to actual file name
Set xlSht = xlBook.ActiveSheet
Set xlCell = xlSht.Range("A:A").Find(myValue) ' set search range

If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp)(2).Value = myValue
MsgBox "Added " & myValue & " to cell " & _
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp).Address(False, False, xlA1, True)
xlBook.Save
xlBook.Close
Else
MsgBox "Found " & myValue & " in cell " & xlCell.Address(False, False, xlA1, True)
End If

xlApp.Quit
Set xlApp = Nothing
End Sub



"Zoltar54" wrote in message
...
Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Z,

You would need to use three Finds to cover all three Comboboxes:

myValue = ComboBox1.Value
' set search range for ComboBox1 Values
Set xlCell = xlSht.Range("A:A").Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, xlCell.Column).End(xlUp)(2).Value = myValue
End If

myValue = ComboBox2.Value
' set search range for ComboBox2 Values
Set xlCell = xlSht.Range("C:C").Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, xlCell.Column).End(xlUp)(2).Value = myValue
End If

myValue = ComboBox3.Value
' set search range for ComboBox3 Values
Set xlCell = xlSht.Range("E:E").Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, xlCell.Column).End(xlUp)(2).Value = myValue
End If

xlBook.Save
xlBook.Close

HTH,
Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

I'm not sure if I follow you. To start, I have two combo boxes that I need
to have update two seperate recordsets. The data consists of types of
facilities ie warehouse, storefront, 3rd part vendor etc. Each combobox is
loaded from a defined range in one excel workbook and are on the same sheet.
As it stands now, there are some names listed in the recordsets however I
need the source data (recordset) to be updated if and when a user enters a
term that is not already on the list. For example warehouse is on the lost
whereas Rail depot is not (and may need to be depending on the user
preference).

Does this clarify my needs further or did I make it worse.

Z



"Bernie Deitrick" wrote:

Z,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes

How many? What naming scheme? What are they returning? How are they related to your database?

which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box,
the program searches the recordset and if the
entry does not exist to add it automatically.

Based on any value (combo-box), or a key value (primatry combo-box)?

Add it to the end of the database? Where is the key value (Sheet name and column) and what data
do
you want to write to the database?

HTH,
Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

Thnaks for the help so far, perhaps I could ask a few more questions?

I have tried the code that you provided and it partially worked. As you
mentioned I didn't provide enough particulars. What else can I provide to
clrify the question?

Z

"Bernie Deitrick" wrote:

Z,

Set a reference to Excel from within your Word project (in the VBE) and use code like this,
below.
You really didn't explain a lot of the particulars, but this should get you going.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlCell As Excel.Range
Dim myValue As String 'Redim to match your user-entered data type

myValue = "Find me" 'Get this from your user's entered value in the combobox

Set xlApp = CreateObject("excel.application")

xlApp.Visible = True ' optional
Set xlBook = xlApp.Workbooks.Open("C:\DeleteMe.xls") ' change to actual file name
Set xlSht = xlBook.ActiveSheet
Set xlCell = xlSht.Range("A:A").Find(myValue) ' set search range

If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp)(2).Value = myValue
MsgBox "Added " & myValue & " to cell " & _
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp).Address(False, False, xlA1, True)
xlBook.Save
xlBook.Close
Else
MsgBox "Found " & myValue & " in cell " & xlCell.Address(False, False, xlA1, True)
End If

xlApp.Quit
Set xlApp = Nothing
End Sub



"Zoltar54" wrote in message
...
Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Bernie,

Very much appreciated. I will give it a go and see if it works.

Thanks agaian,

Z

"Bernie Deitrick" wrote:

Z,

You would need to use three Finds to cover all three Comboboxes:

myValue = ComboBox1.Value
' set search range for ComboBox1 Values
Set xlCell = xlSht.Range("A:A").Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, xlCell.Column).End(xlUp)(2).Value = myValue
End If

myValue = ComboBox2.Value
' set search range for ComboBox2 Values
Set xlCell = xlSht.Range("C:C").Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, xlCell.Column).End(xlUp)(2).Value = myValue
End If

myValue = ComboBox3.Value
' set search range for ComboBox3 Values
Set xlCell = xlSht.Range("E:E").Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, xlCell.Column).End(xlUp)(2).Value = myValue
End If

xlBook.Save
xlBook.Close

HTH,
Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

I'm not sure if I follow you. To start, I have two combo boxes that I need
to have update two seperate recordsets. The data consists of types of
facilities ie warehouse, storefront, 3rd part vendor etc. Each combobox is
loaded from a defined range in one excel workbook and are on the same sheet.
As it stands now, there are some names listed in the recordsets however I
need the source data (recordset) to be updated if and when a user enters a
term that is not already on the list. For example warehouse is on the lost
whereas Rail depot is not (and may need to be depending on the user
preference).

Does this clarify my needs further or did I make it worse.

Z



"Bernie Deitrick" wrote:

Z,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes

How many? What naming scheme? What are they returning? How are they related to your database?

which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box,
the program searches the recordset and if the
entry does not exist to add it automatically.

Based on any value (combo-box), or a key value (primatry combo-box)?

Add it to the end of the database? Where is the key value (Sheet name and column) and what data
do
you want to write to the database?

HTH,
Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

Thnaks for the help so far, perhaps I could ask a few more questions?

I have tried the code that you provided and it partially worked. As you
mentioned I didn't provide enough particulars. What else can I provide to
clrify the question?

Z

"Bernie Deitrick" wrote:

Z,

Set a reference to Excel from within your Word project (in the VBE) and use code like this,
below.
You really didn't explain a lot of the particulars, but this should get you going.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim xlApp As Object
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim xlCell As Excel.Range
Dim myValue As String 'Redim to match your user-entered data type

myValue = "Find me" 'Get this from your user's entered value in the combobox

Set xlApp = CreateObject("excel.application")

xlApp.Visible = True ' optional
Set xlBook = xlApp.Workbooks.Open("C:\DeleteMe.xls") ' change to actual file name
Set xlSht = xlBook.ActiveSheet
Set xlCell = xlSht.Range("A:A").Find(myValue) ' set search range

If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp)(2).Value = myValue
MsgBox "Added " & myValue & " to cell " & _
xlSht.Cells(xlSht.Rows.Count, 1).End(xlUp).Address(False, False, xlA1, True)
xlBook.Save
xlBook.Close
Else
MsgBox "Found " & myValue & " in cell " & xlCell.Address(False, False, xlA1, True)
End If

xlApp.Quit
Set xlApp = Nothing
End Sub



"Zoltar54" wrote in message
...
Hello,

I am currently working on a project in Word VBA. The userform that I have
developed has a number of combo boxes which are populated from an Existing
Excel Workbook. What I would like to have happen is when a user enters
information into the combo box, the program searches the recordset and if the
entry does not exist to add it automatically.

I have asked this question in a few Word NG and have been subsequently
directed to an Excel NG. Any assistance that could be provided would be
greatly appreictaed.

Z

Controling Excel using Word VBA
 

Z,

D'Oh! <Head smack

You'll need to correct my errors:

xlSht.Cells(xlSht.Rows.Count, xlCell.Column).....

Since xlCell is nothing, this will blow up....

Instead, put this declaration at the top
Dim mySRange As Excel.Range


Then use this three times, as in the last example:

myValue = ComboBox1.Value
' set search range for ComboBox1 Values
Set mySRange = xlSht.Range("A:A")
Set xlCell = mySRange.Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, mySRange.Column).End(xlUp)(2).Value = myValue
End If

etc...

Sorry about that...

Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

Very much appreciated. I will give it a go and see if it works.

Thanks agaian,

Z

Controling Excel using Word VBA
 

Asked and answered. I was just about to post a question about that!

Thanks very much, i'll let you know how it turns put.

Z

"Bernie Deitrick" wrote:

Z,

D'Oh! <Head smack

You'll need to correct my errors:

xlSht.Cells(xlSht.Rows.Count, xlCell.Column).....

Since xlCell is nothing, this will blow up....

Instead, put this declaration at the top
Dim mySRange As Excel.Range


Then use this three times, as in the last example:

myValue = ComboBox1.Value
' set search range for ComboBox1 Values
Set mySRange = xlSht.Range("A:A")
Set xlCell = mySRange.Find(myValue)
If xlCell Is Nothing Then ' add the value to the set at the bottom
xlSht.Cells(xlSht.Rows.Count, mySRange.Column).End(xlUp)(2).Value = myValue
End If

etc...

Sorry about that...

Bernie
MS Excel MVP


"Zoltar54" wrote in message
...
Bernie,

Very much appreciated. I will give it a go and see if it works.

Thanks agaian,

Z