Calling Excel cells from Outlook 2

[shavenlunatic]

I wish to have a piece of code in Outlook which will access an excel sheet and put that information into a variable.

Obviously if it was running in excel, this would be easy (a-la "Variable = sheet1.cells(y, x)" ) but im unsure how/if this is to be done

(no doubt, as per usual, 5 minutes after posting this I will figure it out, but thanks in advance if anyone beats me to it)

clueless

 

[xlbo]

you must instantiate an instance of excel and then reference that

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open("Path&Name")
Set xlSheet = xlBook.Worksheets("SheetName")

theValue = xlSheet.Cells(r, c).Value

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shavenlunatic]

copy/pasted that code and ammended the paths, to test...

"User-defined type not defined"



and i just forgetting something stupid i should be doing?

Sub testexcel()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open("\\192.168.41.15\department\VG\VBA\test\test.xls")
Set xlSheet = xlBook.Worksheets("Sheet1")

'theValue = xlSheet.Cells(r, c).Value

End Sub

clueless

 

[HarleyQuinn]

Reference to the Microsoft Excel X.0 Object Library.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[firefytr]

I always like to check if the file is still open, regardless of the calling app ...

Sub testexcel()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object
    Dim strFullName As String, strName As String
    Dim blnCreated As Boolean, blnBookOpen As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    blnCreated = False
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        blnCreated = True
    End If
    strFullName = "C:\Documents and Settings\Rob\Desktop\Book12.xls"
    strName = Right(strFullName, Len(strFullName) - InStrRev(strFullName, "\"))
    xlApp.Visible = True
    If IsExcelBookOpen(strName, blnCreated, xlApp) Then
        Set xlBook = xlApp.workbooks(strName) '("test.xls")
        blnBookOpen = True
    Else
        Set xlBook = xlApp.workbooks.Open(strFullName) '("\\192.168.41.15\department\VG\VBA\test\test.xls")
        blnBookOpen = False
    End If
    Set xlSheet = xlBook.Worksheets("Sheet1")
    MsgBox xlSheet.Cells(1, 1).Value
    If Not blnBookOpen Then xlBook.Close savechanges:=False
    If blnCreated Then xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

Function IsExcelBookOpen(wbName As String, IsCreated As Boolean, varApp As Object) As Boolean
    On Error Resume Next
    IsExcelBookOpen = False
    If IsCreated Then Exit Function
    If varApp Is Nothing Then Exit Function
    If InStr(1, varApp.Caption, "Excel") = 0 Then Exit Function
    IsExcelBookOpen = Len(varApp.workbooks(wbName).Name)
End Function

-----------
Regards,
Zack Barresse

 

[TomCarnahan]

In the VBA editor, go to your menu and select:

[ol]
[li]Menu[/li]
[li]Tools[/li]
[li]References[/li]
[/ol]

When the "References" dialog pops up, it should look like
this.

If you don't see "Microsoft Excel", scroll down. Everything is in alpha order [Note: all the office DLLs list starting with "Microsoft".]

Once you have set the reference, I would recommend declaring all your Excel objects explicitly. Example:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
' etc.

Also, set your VBA Editor "Options" like this. When you type "xlApp.", as soon as you type the "dot", if you have done all the above, the editor will offer you a "picklist" of acceptable properties and methods that you can use with that object.

Last tip: provided that you have done all of the above, the "experts" (which I do not consider myself) recommend that by declaring your objects first, you type everything in lower-case[/]. When you click off the line, all the recognized commands and variables (if you declare them upper and lower case) will revert to their mixed case. The things you mispelled or it doesn't recognize stay lower-case.

Hope that gives you a little mileage ... have fun! ;-)

-- Tom <><

 

[xlbo]

good tips Tom - especially the lower case one - have a star

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Note: the "pick-list" is called Intellisense.

And there is always going to be a huge debate over variable naming conventions. I generally suggest a cross between hungarian and proper english terms, i.e. blnWasCreated, rngSummary, lngRow, etc.

I realize that Early Binding is generally always desired, but sometimes Late Binding can have it's advantages, which I think are primarily due to the user's situation(s).

-----------
Regards,
Zack Barresse

 

[xlbo]

I would certainly agree with that - a 3 char lowercase indication of the variable data type followed by something that describes what the variable is used for

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Late Binding can have it's advantages
Mainly if you have to deploy your app in a mixed office environment.

 

[TomCarnahan]

PHV,

I agree with your point about late binding. I choose to use the best of both worlds.

I use early binding (if it is available) for development so that I can make use of Intellisense ... I use it so much, I forgot it had a name.

Then if there is an advantage to late binding, I convert over to late binding.

I like the idea about using a cross between Hungarian and proper english. I also like a 3 or more character lower-case prefix to indicate data type, but I go one step further.

For regular variables, I use something like: bln_IsVisible, int_MyNumber, dbl_MyOtherNumber

For constants, I precede the 3 character prefix with the lowercase "c". Example: cint_MyNumber, etc.

If the variable or constant is scoped at the module-level, I tack on an "m". Example: mcint_MyNumber, mbln_IsTrue

This last convention tells me to look in the module declaration area to find the dimensioning statement.

Finally, if the variable is a global or publicly declared constant or variable, I put all of those in one standard module (named "mod_Globals"; all dimension statements, no subs or functions) and the statements would look like the following:

Public Constant gcdbl_PI as Double = 3.14 'etc
Public gbln_ConditionIsTrue as Boolean

This convention tells me that I can find the constant or variable in one specific module, whether the item is a constant or variable, that it is globally scoped, and gives me its datatype.

From my previous Oracle background, I use the underscore to set off the prefix ... personal preference ... makes it easier for me to visually parse the name and the prefix tag.

All of this is a cross between some defacto standards and personal preference. There are probably folks who would get all over my case for inventing another style, but I find it conveys more of the info that I want and is easier to understand.

Cheers!

-- Tom Carnahan <><