| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA does not work!!
In my following VBA (in ms project) i open a excel file and with a vlookup i
search for info. but not every vlookup will end up with a result thus it will give error 1004. so far not a problem, here is the part of the code which handles the error Set test = Nothing On Error Resume Next test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) If Err.Number = 1004 Then TempPercent = 0 ProjectTaskT.Notes = "activiteit niet gevonden" Else TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) End If When i run the macro for the first time it will do the trick. but the 2nd time it gives the result TempPercent = 0 every time also when there is a match. It looks like that the Err.Number hangs.. Does anybody have a clue how to solve this problem. greetings Robert Heuveling Here is the complete code: Sub Update_Percentage2() ' Deze macro haalt de technische voortgangscijfers uit een excel file en plaatst deze in Fysiek percentage voltooid. Dim xlApp As Excel.Application Dim FilesParent, ProjectTasks As Tasks Dim FileT, ProjectTaskT As Task Dim Proj As MSProject.Application Dim SpreadsheetName, XLSNameWithPath, TaskAct, Perccheck As String Dim TempPercent As Integer Dim test As Variant Set Proj = GetObject(, "MSProject.Application") ' Hier moet de filenaam van de excelfile worden gegeven XLSNameWithPath = InputBox("geef de filename (+ pad) van de update file") ' ' Hier wordt de excel file geopend Set xlApp = New Excel.Application xlApp.Visible = True xlApp.Workbooks.Open FileName:=XLSNameWithPath ' Hier wordt per activiteit gekeken of deze bestaat in excel en plaatst vervolgens de percentage in het projectbestand For Each ProjectTaskT In Proj.Application.ActiveProject.Tasks ' Gebruikt VLookUp (Verticaal zoeken om het % techn complete te vinden If ProjectTaskT.Summary = False Then TaskAct = ProjectTaskT.Text2 Set test = Nothing On Error Resume Next test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) If Err.Number = 1004 Then TempPercent = 0 ProjectTaskT.Notes = "activiteit niet gevonden" Else TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) End If ProjectTaskT.PhysicalPercentComplete = TempPercent End If Next ProjectTaskT ' Sluit MS Excel xlApp.Visible = False xlApp.Workbooks.Close xlApp.Quit Set xlApp = Nothing End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My VBA does not work!!
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My VBA does not work!!
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My VBA does not work!!
Robert,
You have too many and too few objects he test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) xlApp.WorksheetFunction.VLookup is sufficient. Also, using automation, always make sure all your object reference go through xlApp so you avoid unqualified references. So you have something like: Dim WB as workbook Dim WS as worksheet Dim test as variant set wb=xlapp.workbooks.open(<Path&Filename) set ws=wb.worksheets("test1") 'Not sure what TaskAct refers to test = xlApp.WorksheetFunction.VLookup(<TaskAct, WS.Range("C:G"), 5, False) 'etc NickHK "Robert Heuveling" wrote in message ... In my following VBA (in ms project) i open a excel file and with a vlookup i search for info. but not every vlookup will end up with a result thus it will give error 1004. so far not a problem, here is the part of the code which handles the error Set test = Nothing On Error Resume Next test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) If Err.Number = 1004 Then TempPercent = 0 ProjectTaskT.Notes = "activiteit niet gevonden" Else TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) End If When i run the macro for the first time it will do the trick. but the 2nd time it gives the result TempPercent = 0 every time also when there is a match. It looks like that the Err.Number hangs.. Does anybody have a clue how to solve this problem. greetings Robert Heuveling Here is the complete code: Sub Update_Percentage2() ' Deze macro haalt de technische voortgangscijfers uit een excel file en plaatst deze in Fysiek percentage voltooid. Dim xlApp As Excel.Application Dim FilesParent, ProjectTasks As Tasks Dim FileT, ProjectTaskT As Task Dim Proj As MSProject.Application Dim SpreadsheetName, XLSNameWithPath, TaskAct, Perccheck As String Dim TempPercent As Integer Dim test As Variant Set Proj = GetObject(, "MSProject.Application") ' Hier moet de filenaam van de excelfile worden gegeven XLSNameWithPath = InputBox("geef de filename (+ pad) van de update file") ' ' Hier wordt de excel file geopend Set xlApp = New Excel.Application xlApp.Visible = True xlApp.Workbooks.Open FileName:=XLSNameWithPath ' Hier wordt per activiteit gekeken of deze bestaat in excel en plaatst vervolgens de percentage in het projectbestand For Each ProjectTaskT In Proj.Application.ActiveProject.Tasks ' Gebruikt VLookUp (Verticaal zoeken om het % techn complete te vinden If ProjectTaskT.Summary = False Then TaskAct = ProjectTaskT.Text2 Set test = Nothing On Error Resume Next test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) If Err.Number = 1004 Then TempPercent = 0 ProjectTaskT.Notes = "activiteit niet gevonden" Else TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) End If ProjectTaskT.PhysicalPercentComplete = TempPercent End If Next ProjectTaskT ' Sluit MS Excel xlApp.Visible = False xlApp.Workbooks.Close xlApp.Quit Set xlApp = Nothing End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
My VBA does not work!!
I still don't know why but this change did the trick. and the code does look
a lot cleaner now. Many thanks... Greetings Robert Heuveling "NickHK" wrote: Robert, You have too many and too few objects he test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) xlApp.WorksheetFunction.VLookup is sufficient. Also, using automation, always make sure all your object reference go through xlApp so you avoid unqualified references. So you have something like: Dim WB as workbook Dim WS as worksheet Dim test as variant set wb=xlapp.workbooks.open(<Path&Filename) set ws=wb.worksheets("test1") 'Not sure what TaskAct refers to test = xlApp.WorksheetFunction.VLookup(<TaskAct, WS.Range("C:G"), 5, False) 'etc NickHK "Robert Heuveling" wrote in message ... In my following VBA (in ms project) i open a excel file and with a vlookup i search for info. but not every vlookup will end up with a result thus it will give error 1004. so far not a problem, here is the part of the code which handles the error Set test = Nothing On Error Resume Next test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) If Err.Number = 1004 Then TempPercent = 0 ProjectTaskT.Notes = "activiteit niet gevonden" Else TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) End If When i run the macro for the first time it will do the trick. but the 2nd time it gives the result TempPercent = 0 every time also when there is a match. It looks like that the Err.Number hangs.. Does anybody have a clue how to solve this problem. greetings Robert Heuveling Here is the complete code: Sub Update_Percentage2() ' Deze macro haalt de technische voortgangscijfers uit een excel file en plaatst deze in Fysiek percentage voltooid. Dim xlApp As Excel.Application Dim FilesParent, ProjectTasks As Tasks Dim FileT, ProjectTaskT As Task Dim Proj As MSProject.Application Dim SpreadsheetName, XLSNameWithPath, TaskAct, Perccheck As String Dim TempPercent As Integer Dim test As Variant Set Proj = GetObject(, "MSProject.Application") ' Hier moet de filenaam van de excelfile worden gegeven XLSNameWithPath = InputBox("geef de filename (+ pad) van de update file") ' ' Hier wordt de excel file geopend Set xlApp = New Excel.Application xlApp.Visible = True xlApp.Workbooks.Open FileName:=XLSNameWithPath ' Hier wordt per activiteit gekeken of deze bestaat in excel en plaatst vervolgens de percentage in het projectbestand For Each ProjectTaskT In Proj.Application.ActiveProject.Tasks ' Gebruikt VLookUp (Verticaal zoeken om het % techn complete te vinden If ProjectTaskT.Summary = False Then TaskAct = ProjectTaskT.Text2 Set test = Nothing On Error Resume Next test = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) If Err.Number = 1004 Then TempPercent = 0 ProjectTaskT.Notes = "activiteit niet gevonden" Else TempPercent = xlApp.ActiveWorkbook.Application.WorksheetFunction .VLookup(TaskAct, Sheets("test1").Range("C:G"), 5, False) End If ProjectTaskT.PhysicalPercentComplete = TempPercent End If Next ProjectTaskT ' Sluit MS Excel xlApp.Visible = False xlApp.Workbooks.Close xlApp.Quit Set xlApp = Nothing End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to update a column in a work based on another work sheet | New Users to Excel | |||
| how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
| flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Hybrid Mode
