Excel file has the following:

Code Size Price Description
#12345 X $23 T-shirt
#12345 Y $20 T-shirt
#12333 X $100 Computer

I am creating InputBox in word, to get the code and size from the user. I'm
able to create a link between Excel and word
(http://word.mvps.org/faqs/interdev/c...xlfromword.htm)

Question: how do I do a search for that code and size and show the Price and
Description into the table (row) in my word document?

Thanks in advance!

Look in Excel VBA help at the sample code for the find next statement.

Find the code and then check each found cell to see if it is the correct
size, When you match both, get the price.

--
Regards,
Tom Ogilvy


"User" wrote:

Excel file has the following:

Code Size Price Description
#12345 X $23 T-shirt
#12345 Y $20 T-shirt
#12333 X $100 Computer

I am creating InputBox in word, to get the code and size from the user. I'm
able to create a link between Excel and word
(http://word.mvps.org/faqs/interdev/c...xlfromword.htm)

Question: how do I do a search for that code and size and show the Price and
Description into the table (row) in my word document?

Thanks in advance!

Thanks for your reply.

Now I'm getting "c:\....\file.xls caused a problem. Method 'worksheets' of
object '_global' failed.

FYI, i added the "With Worksheets(1).Range("a1:a500")" found from the
example in my Word macro . My full source:

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True
here; but your code will run faster if you don't make it visible
oXL.Visible = True

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here

'Input Style here
Dim Message, Title, Default, Style, c
Message = "Please Enter Style #" ' Set prompt.
Title = "Enter Style #" ' Set title.
' Display message, title, and default value.
Style = inputBox(Message, Title)

'Search for style's row
With Worksheets(1).Range("h1:h500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With





'get next sheet
Next oSheet

' If ExcelWasNotRunning Then
' oXL.Quit
'End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

"Tom Ogilvy" wrote:

Look in Excel VBA help at the sample code for the find next statement.

Find the code and then check each found cell to see if it is the correct
size, When you match both, get the price.

--
Regards,
Tom Ogilvy


"User" wrote:

Excel file has the following:

Code Size Price Description
#12345 X $23 T-shirt
#12345 Y $20 T-shirt
#12333 X $100 Computer

I am creating InputBox in word, to get the code and size from the user. I'm
able to create a link between Excel and word
(http://word.mvps.org/faqs/interdev/c...xlfromword.htm)

Question: how do I do a search for that code and size and show the Price and
Description into the table (row) in my word document?

Thanks in advance!

is this
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

all on one line? You can't break a string on multiple lines.

If that isn't the problem, does the file exist?

--
Regards,
Tom Ogilvy


"User" wrote:

Thanks for your reply.

Now I'm getting "c:\....\file.xls caused a problem. Method 'worksheets' of
object '_global' failed.

FYI, i added the "With Worksheets(1).Range("a1:a500")" found from the
example in my Word macro . My full source:

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True
here; but your code will run faster if you don't make it visible
oXL.Visible = True

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here

'Input Style here
Dim Message, Title, Default, Style, c
Message = "Please Enter Style #" ' Set prompt.
Title = "Enter Style #" ' Set title.
' Display message, title, and default value.
Style = inputBox(Message, Title)

'Search for style's row
With Worksheets(1).Range("h1:h500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With





'get next sheet
Next oSheet

' If ExcelWasNotRunning Then
' oXL.Quit
'End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

"Tom Ogilvy" wrote:

Look in Excel VBA help at the sample code for the find next statement.

Find the code and then check each found cell to see if it is the correct
size, When you match both, get the price.

--
Regards,
Tom Ogilvy


"User" wrote:

Excel file has the following:

Code Size Price Description
#12345 X $23 T-shirt
#12345 Y $20 T-shirt
#12333 X $100 Computer

I am creating InputBox in word, to get the code and size from the user. I'm
able to create a link between Excel and word
(http://word.mvps.org/faqs/interdev/c...xlfromword.htm)

Question: how do I do a search for that code and size and show the Price and
Description into the table (row) in my word document?

Thanks in advance!

Yep that file exist, and it's all in 1 line.

The XLS file does load up , and i got ot switch back to the ms word document
to type in the style, and after pressing enter, that's where i get the error.

Also is it possible for me to do a:

when user types in a style code, it will search the excel file, and show a
drop down list of the available sizes, and after selecting the size, click
OK, and the size and price will be added into the MS Word table. Is this
possible?

"Tom Ogilvy" wrote:

is this
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

all on one line? You can't break a string on multiple lines.

If that isn't the problem, does the file exist?

--
Regards,
Tom Ogilvy


"User" wrote:

Thanks for your reply.

Now I'm getting "c:\....\file.xls caused a problem. Method 'worksheets' of
object '_global' failed.

FYI, i added the "With Worksheets(1).Range("a1:a500")" found from the
example in my Word macro . My full source:

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True
here; but your code will run faster if you don't make it visible
oXL.Visible = True

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here

'Input Style here
Dim Message, Title, Default, Style, c
Message = "Please Enter Style #" ' Set prompt.
Title = "Enter Style #" ' Set title.
' Display message, title, and default value.
Style = inputBox(Message, Title)

'Search for style's row
With Worksheets(1).Range("h1:h500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With





'get next sheet
Next oSheet

' If ExcelWasNotRunning Then
' oXL.Quit
'End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

"Tom Ogilvy" wrote:

Look in Excel VBA help at the sample code for the find next statement.

Find the code and then check each found cell to see if it is the correct
size, When you match both, get the price.

--
Regards,
Tom Ogilvy


"User" wrote:

Excel file has the following:

Code Size Price Description
#12345 X $23 T-shirt
#12345 Y $20 T-shirt
#12333 X $100 Computer

I am creating InputBox in word, to get the code and size from the user. I'm
able to create a link between Excel and word
(http://word.mvps.org/faqs/interdev/c...xlfromword.htm)

Question: how do I do a search for that code and size and show the Price and
Description into the table (row) in my word document?

Thanks in advance!

Found out that i need to add oWB.worksheet(1) blah blah ..

but can you answer me on the other post?

Thanks in advance again! :)

"Tom Ogilvy" wrote:

is this
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

all on one line? You can't break a string on multiple lines.

If that isn't the problem, does the file exist?

--
Regards,
Tom Ogilvy


"User" wrote:

Thanks for your reply.

Now I'm getting "c:\....\file.xls caused a problem. Method 'worksheets' of
object '_global' failed.

FYI, i added the "With Worksheets(1).Range("a1:a500")" found from the
example in my Word macro . My full source:

Sub WorkOnAWorkbook()

Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String

'specify the workbook to work on
WorkbookToWorkOn = "C:\Program Files\Symantec\ACT\Template\Custom
Templates\cliffs7.xls"

'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True
here; but your code will run faster if you don't make it visible
oXL.Visible = True

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here

'Input Style here
Dim Message, Title, Default, Style, c
Message = "Please Enter Style #" ' Set prompt.
Title = "Enter Style #" ' Set title.
' Display message, title, and default value.
Style = inputBox(Message, Title)

'Search for style's row
With Worksheets(1).Range("h1:h500")
Set c = .Find(2, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With





'get next sheet
Next oSheet

' If ExcelWasNotRunning Then
' oXL.Quit
'End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

"Tom Ogilvy" wrote:

Look in Excel VBA help at the sample code for the find next statement.

Find the code and then check each found cell to see if it is the correct
size, When you match both, get the price.

--
Regards,
Tom Ogilvy


"User" wrote:

Excel file has the following:

Code Size Price Description
#12345 X $23 T-shirt
#12345 Y $20 T-shirt
#12333 X $100 Computer

I am creating InputBox in word, to get the code and size from the user. I'm
able to create a link between Excel and word
(http://word.mvps.org/faqs/interdev/c...xlfromword.htm)

Question: how do I do a search for that code and size and show the Price and
Description into the table (row) in my word document?

Thanks in advance!