Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't run on new machine
Hi There I wrote the following macro and this runs fine without any problems on one machine. However I need it to run on another machine in my office but when I try I get the following error message: Run-time error '-2147319779 (8002801d)': Automation error Library not registered Macro: Sub auto_open() Windows("EIS Job Log test.xls").Activate Range("B2").Select Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\EIS Requests\" dattim = Format(Date, "yyyymmdd") & " " & "Time-" & Format(Time, "hhmmss") For i = Fldr.Items.Count To 1 Step -1 Range("A1").Select rowlength = Selection.CurrentRegion.Rows.Count Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS_REQUEST") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" open1 = MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" filenm = Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" End If Next olAtt olMi.save olMi.Move MoveToFldr Workbooks.Open Filename:=open1 'copies and pastes data from eis request Range("IR4:IV4").Select Selection.Copy Windows("EIS Job Log test.xls").Activate Range("A1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 1).Select End If Next x Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'copies and pastes filename Range("E1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 6).Select End If Next x ActiveCell = filenm Windows(filenm).Activate ActiveWorkbook.Close False Windows("EIS Job Log test.xls").Activate ActiveWorkbook.save ActiveWorkbook.Close False End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Both machines have the following ticked in the reference table. Visual Basic for Applications Microsoft Excel 9.0 Object Library OLE Automation Microsoft Office Object Library Microsoft Outlook Object Libray Can anyone help? I would be extremely gratetful Thanks in advance Jamie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't run on new machine
You need to create a reference to the Outlook Library. Click on Tools,
References from your IDE. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Jamie" wrote: Hi There I wrote the following macro and this runs fine without any problems on one machine. However I need it to run on another machine in my office but when I try I get the following error message: Run-time error '-2147319779 (8002801d)': Automation error Library not registered Macro: Sub auto_open() Windows("EIS Job Log test.xls").Activate Range("B2").Select Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\EIS Requests\" dattim = Format(Date, "yyyymmdd") & " " & "Time-" & Format(Time, "hhmmss") For i = Fldr.Items.Count To 1 Step -1 Range("A1").Select rowlength = Selection.CurrentRegion.Rows.Count Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS_REQUEST") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" open1 = MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" filenm = Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" End If Next olAtt olMi.save olMi.Move MoveToFldr Workbooks.Open Filename:=open1 'copies and pastes data from eis request Range("IR4:IV4").Select Selection.Copy Windows("EIS Job Log test.xls").Activate Range("A1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 1).Select End If Next x Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'copies and pastes filename Range("E1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 6).Select End If Next x ActiveCell = filenm Windows(filenm).Activate ActiveWorkbook.Close False Windows("EIS Job Log test.xls").Activate ActiveWorkbook.save ActiveWorkbook.Close False End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Both machines have the following ticked in the reference table. Visual Basic for Applications Microsoft Excel 9.0 Object Library OLE Automation Microsoft Office Object Library Microsoft Outlook Object Libray Can anyone help? I would be extremely gratetful Thanks in advance Jamie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't run on new machine
Hi Galimi
Thanks for your response. I thought I had done this already. When I click references it shows that all these are ticked: Visual Basic for Applications Microsoft Excel 9.0 Object Library OLE Automation Microsoft Office Object Library Microsoft Outlook Object Libray Do I need to do something else? Jamie "galimi" wrote: You need to create a reference to the Outlook Library. Click on Tools, References from your IDE. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Jamie" wrote: Hi There I wrote the following macro and this runs fine without any problems on one machine. However I need it to run on another machine in my office but when I try I get the following error message: Run-time error '-2147319779 (8002801d)': Automation error Library not registered Macro: Sub auto_open() Windows("EIS Job Log test.xls").Activate Range("B2").Select Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\EIS Requests\" dattim = Format(Date, "yyyymmdd") & " " & "Time-" & Format(Time, "hhmmss") For i = Fldr.Items.Count To 1 Step -1 Range("A1").Select rowlength = Selection.CurrentRegion.Rows.Count Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS_REQUEST") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" open1 = MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" filenm = Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" End If Next olAtt olMi.save olMi.Move MoveToFldr Workbooks.Open Filename:=open1 'copies and pastes data from eis request Range("IR4:IV4").Select Selection.Copy Windows("EIS Job Log test.xls").Activate Range("A1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 1).Select End If Next x Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'copies and pastes filename Range("E1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 6).Select End If Next x ActiveCell = filenm Windows(filenm).Activate ActiveWorkbook.Close False Windows("EIS Job Log test.xls").Activate ActiveWorkbook.save ActiveWorkbook.Close False End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Both machines have the following ticked in the reference table. Visual Basic for Applications Microsoft Excel 9.0 Object Library OLE Automation Microsoft Office Object Library Microsoft Outlook Object Libray Can anyone help? I would be extremely gratetful Thanks in advance Jamie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't run on new machine
Hi Jamie, your routine was a bit of a puzzle to me, but I was able to
duplicate your error and resolve it. I experienced a similar error when I purposely referenced the wrong path that stores the EIS requests Spreadsheet attachments. Apparently, Your routine saves attachments to "I:\EIS\Forms\EIS Requests\" ... The new computer you are using may not use "I" as the drive that maps to the EIS Request folder..or it may not even be able to access this shared location. If the folder "EIS\Forms\EIS Requests" is not on the "I" drive, you will receive this error. Suggestion 1) Change MyPath = "I:\EIS\Forms\EIS Requests\" To = MyPath ="\\YourServerLocation\EIS\Forms\EIS Requests\ or temporarily create a folder on the C-drive ("C:\EIS\Forms\EIS Requests\") and make "MyPath" reference that path to see if you still receive the same error. if so, h 2) Make sure Outlook has been Activated, set up and in use on the new machine. I noticed your routine also requires a subfolder "EisReq" to exist within the inbox of Microsoft Outlook, an error will occur if this sub-folder does not exist. p.s. You have a nice routine. I have a small suggestion. You could easily develop a "Button" and place it directly on your spreadsheet to activates the process whenever desired. One click of a button could scan inbox for requests, move all requests to the EISReq folder, save all attachments to designated folder and add new requests to EIS Request Log spreadsheet. I created a sample spreadsheet if you want to see it. Let me know if you have any questions. Reggie. "Jamie" wrote: Hi Galimi Thanks for your response. I thought I had done this already. When I click references it shows that all these are ticked: Visual Basic for Applications Microsoft Excel 9.0 Object Library OLE Automation Microsoft Office Object Library Microsoft Outlook Object Libray Do I need to do something else? Jamie "galimi" wrote: You need to create a reference to the Outlook Library. Click on Tools, References from your IDE. -- http://HelpExcel.com 1-888-INGENIO 1-888-464-3646 x0197758 "Jamie" wrote: Hi There I wrote the following macro and this runs fine without any problems on one machine. However I need it to run on another machine in my office but when I try I get the following error message: Run-time error '-2147319779 (8002801d)': Automation error Library not registered Macro: Sub auto_open() Windows("EIS Job Log test.xls").Activate Range("B2").Select Dim olApp As Outlook.Application Dim olNs As NameSpace Dim Fldr As MAPIFolder Dim MoveToFldr As MAPIFolder Dim olMi As MailItem Dim olAtt As Attachment Dim MyPath As String Dim i As Long Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set Fldr = olNs.GetDefaultFolder(olFolderInbox) Set MoveToFldr = Fldr.Folders("eisreq") MyPath = "I:\EIS\Forms\EIS Requests\" dattim = Format(Date, "yyyymmdd") & " " & "Time-" & Format(Time, "hhmmss") For i = Fldr.Items.Count To 1 Step -1 Range("A1").Select rowlength = Selection.CurrentRegion.Rows.Count Set olMi = Fldr.Items(i) If InStr(1, olMi.Subject, "EIS_REQUEST") 0 Then For Each olAtt In olMi.Attachments If olAtt.Filename = "EIS Request.xls" Then olAtt.SaveAsFile MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" open1 = MyPath & Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" filenm = Fldr.Items.Count & " " & olMi.SenderName & " " & "Date-" & dattim & ".xls" End If Next olAtt olMi.save olMi.Move MoveToFldr Workbooks.Open Filename:=open1 'copies and pastes data from eis request Range("IR4:IV4").Select Selection.Copy Windows("EIS Job Log test.xls").Activate Range("A1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 1).Select End If Next x Selection.PasteSpecial paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False 'copies and pastes filename Range("E1").Select For x = 1 To rowlength If ActiveCell.Cells < "" Then Cells(ActiveCell.Row + 1, 6).Select End If Next x ActiveCell = filenm Windows(filenm).Activate ActiveWorkbook.Close False Windows("EIS Job Log test.xls").Activate ActiveWorkbook.save ActiveWorkbook.Close False End If Next i Set olAtt = Nothing Set olMi = Nothing Set Fldr = Nothing Set MoveToFldr = Nothing Set olNs = Nothing Set olApp = Nothing End Sub Both machines have the following ticked in the reference table. Visual Basic for Applications Microsoft Excel 9.0 Object Library OLE Automation Microsoft Office Object Library Microsoft Outlook Object Libray Can anyone help? I would be extremely gratetful Thanks in advance Jamie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel as an Adding Machine | Excel Discussion (Misc queries) | |||
Reference & update destin. cells on 1st machine from source workbook on 2nd machine. | Excel Discussion (Misc queries) | |||
Macro code error; machine dependent | Excel Worksheet Functions | |||
Is there any API to know that ocx files are registerd in the machine or not | Excel Programming | |||
newbie - how to save macro for use on another machine | Excel Programming |