0

I created a macro in Outlook which needs Word and Excel references. It has to work on different versions of Office (2007, 2010, 2013). I have Office 2013, so when I "install" macro (copy VBAProject.otm) on 2007 and 2010 I have missing references. I wanted to add references manually using code similar to this in Excel:

ThisWorkbook.VBProject.References.AddFromFile

but I couldn't find anything like that. I searched internet, but I also failed. Can anyone tell me if in Outlook VBA is possible to add reference from file?

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
dexter910
  • 11
  • 1
  • 4

1 Answers1

1

To use unreferenced libraries and avoid setting references on each users' computer you access them by creating objects via late binding. This is typically done using the CreateObject method e.g for the scripting runtime you'd use:

Dim fso as object

Set fso = CreateObject("Microsoft.ScriptingRuntime")

You can search online for how to late bind the particular reference you require.

Absinthe
  • 3,258
  • 6
  • 31
  • 70
  • More specifically and to the point, for Outlook, it would be `Set OLApp = CreateObject("Outlook.Application").` – Scott Holtzman Nov 14 '16 at 16:36
  • Thanks for the reference. I thought a broad answer would be suitable as it's a big area to study if you don't know about early/late binding. – Absinthe Nov 14 '16 at 16:41
  • Nah, it'd be `Set oXL = CreateObject("Excel.Application")` or `Set oXL = GetObject(, "Excel.Application")` if Excel's already running - code is in Outlook, so no need for Outlook references. Upvoted anyway - it's about the early/late binding that needs looking into. – Darren Bartrup-Cook Nov 14 '16 at 16:41
  • Yes, but when I turn off Excel reference then code below is not working: `xlSheet.Range("B1:I" & row).Sort Key1:=xlSheet.Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal` – dexter910 Nov 14 '16 at 17:43
  • @ dexter910 did you set an Excel reference in code like we suggested? Post your current code. What error did you get, if any? – Absinthe Nov 14 '16 at 17:48
  • Here is my code: `Dim xlapp As Object Dim xlWB As Object Dim xlSheet As Object Set xlapp = CreateObject("Excel.Application") Set xlWB = xlapp.Workbooks.Open(strPath) Set xlSheet = xlWB.Sheets(1) Const xlUp As Long = -4162 row = xlSheet.Range("A" & xlSheet.Rows.Count).End(xlUp).row + 1 xlSheet.Range("A" & row) = TextBox2.Text xlSheet.Range("A1:A" & row).Sort Key1:=xlSheet.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _DataOption1:=xlSortNormal` – dexter910 Nov 14 '16 at 17:56
  • I don't know if there is any way to write this code in separeted lines – dexter910 Nov 14 '16 at 17:58
  • Sorry, there is error, when I want to sort rows: vba runtime error 1004 sort method of range class failed – dexter910 Nov 14 '16 at 18:42
  • @ dexter910 which line do you get the error on? What is the value of your row variable and do you use the Sort.Apply method in your sort? – Absinthe Nov 14 '16 at 23:11
  • `Const xlUp As Long = -4162 row = xlSheet.Range("A" & xlSheet.Rows.Count).End(xlUp).row`. I didn't use apply method. With Excel reference there was no need for that. I solved problem when I used this code: 'xlSheet.Range("B2:I" & row).Sort Key1:=xlSheet.Range("B1"), Order1:=1' – dexter910 Nov 15 '16 at 10:04