put cell valve in a text box

[s_smith_iet]

I posted this today but I don't know if I was clear in what I was
asking.....
I need to put a cell value of an unopened excel sheet in a text box.
When I open outlook a user form automatically comes up with a bunch of
text boxes that I need filled with certain cells (they are formulas).

The cells are in the following excel sheet

\\Mascarolinabdc\puball\Newport Precision\Spreadsheets\NPI PVD Log
Sheet.xls
The sheets are "maintenance 1", "maintenance 2", and "maintenance 3"

 

[Joel]

Sub getdata()


FName = "\\Mascarolinabdc\puball\Newport Precision\" & _
"Spreadsheets\NPI PVD Log Sheet.xls"
Set databk = Workbooks.Open(Filename:=FName)

With databk

Item1 = .Sheets("maintenance 1").Range("A1").Value
Item2 = .Sheets("maintenance 2").Range("A1").Value
Item3 = .Sheets("maintenance 3").Range("A1").Value

End With
databk.Close

End Sub

 

[s_smith_iet]

where does this go?

I have put it in my form but nothing...
how do I get item1 into textbox 1?
I used textbox.value = item 1 but that doesnt work

I also changed item1 to textbox1 and it didn't work

Please help

Thanks

 

[Joel]

It help to know if there was an error or the data just didn't get into the
textbox. It is hard to tell what you mean when you say that it didn't work.
You have a space between the word Item and the number 1. Not sure if that is
in you code.

The best way to debug the problem is to add msgbox(s) to see if the problem
is reading the workbooks or writing to the text box. Try this modified code.
Check to see if a new excel workbook opens when the code runs. Let me know
exactly what any error message are displayed and the line which is
highlighted in yellow because this greatly helps solve the problems.

Sub getdata()


FName = "\\Mascarolinabdc\puball\Newport Precision\" & _
"Spreadsheets\NPI PVD Log Sheet.xls"
Set databk = Workbooks.Open(Filename:=FName)

With databk

Item1 = .Sheets("maintenance 1").Range("A1").Value
textbox1.value = item1
msgbox("Item 1 = " & Item1)
Item2 = .Sheets("maintenance 2").Range("A1").Value
textbox1.value = item2
msgbox("Item 2 = " & Item2)
Item3 = .Sheets("maintenance 3").Range("A1").Value
textbox1.value = item3
msgbox("Item 3 = " & Item3)

End With
databk.Close

End Sub

 

[s_smith_iet]

Joel,

It works !!!

with one problem.....
WHen I put the code into excel no problem but when I put the dialog
box in outlook it won't open

I get object not found when it trys to open the spread sheet to get
the info.

Is there something special about outlook?

The email exchage server is not on our local server but instead at our
corporate office...is that the reason?

 

[Joel]

There are two flavors or Macro code. the one that works with Excel and the
one that works with other Office products. Outlook doesn't understand a
workbook but can open an excel object. Lokk at this code

Sub test()

Folder = "C:\Documents and Settings\Guest\My Documents\"
FName = "book1.xls"

Set exelbook = GetObject(Folder & FName)

With exelbook
.Application.Visible = True
.Parent.Windows(FName).Visible = True

Item1 = .Sheets("maintenance 1").Range("A1").Value
textbox1.Value = Item1
MsgBox ("Item 1 = " & Item1)
Item2 = .Sheets("maintenance 2").Range("A1").Value
textbox1.Value = Item2
MsgBox ("Item 2 = " & Item2)
Item3 = .Sheets("maintenance 3").Range("A1").Value
textbox1.Value = Item3
MsgBox ("Item 3 = " & Item3)

.Save
.Close
End With

End Sub