Excel crashing

[Steve]

Hi. I am trying to import an aspx file that converts to an excel file, then I
save the excel file and transfer outline level 1 %complete from project to
the excel file. It will not work however, it either crashes or gives me no
update or excel loads but does not show the workbook. Here is my code: [the
web address and filepath you see are not the correct ones, but that shouldn't
matter]

Sub SaveWebData()

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")

IE.Navigate "http://www.data.com/ExportProjectList.aspx" 'load web
page
While IE.busy
DoEvents 'wait until IE is done loading page.
Wend
IE.Visible = False
'ie is now done loading the page

End Sub

Sub UpdatePercentComplete()

Dim ExcelSheet As Excel.Workbook
Dim xlRange As Excel.Range
Dim Tsk As Task

Set ExcelSheet = GetObject("C:\Excel.xls")
ExcelSheet.Application.Visible = True
Set xlRange = ExcelSheet.Worksheets("FileName").Range("K2")
ExcelSheet.Application.ScreenUpdating = False

For Each Tsk In ActiveProject.Tasks
If Not Tsk Is Nothing Then
If Tsk.OutlineLevel = 1 Then
With xlRange
.Range("K2") = (Tsk.PercentComplete / 100)
End With
Set xlRange = xlRange.Offset(1, 0)
End If
End If
Next Tsk

ExcelSheet.Application.ScreenUpdating = True
ExcelSheet.Save
ExcelSheet.Application.Quit
Set ExcelSheet = Nothing

End Sub

Sub CostReport()

Dim StartTime As Date

StartTime = Now
SaveWebData
Do While DateDiff("s", StartTime, Now) < 15
Loop
UpdatePercentComplete

End Sub

The Loop in this routine gives the user enough time to click save from the
aspx file and click yes if there is an existing file with the same name.

 

[Curmudgeon]

Hi. I am trying to import an aspx file that converts to an excel file, then I
save the excel file and transfer outline level 1 %complete from project to
the excel file. It will not work however, it either crashes or gives me no
update or excel loads but does not show the workbook. Here is my code: [the
web address and filepath you see are not the correct ones, but that shouldn't
matter]

Sub SaveWebData()

    Dim IE As Object

        Set IE = CreateObject("InternetExplorer.Application")

        IE.Navigate "http://www.data.com/ExportProjectList.aspx" 'load web
page
        While IE.busy
            DoEvents  'wait until IE is done loading page.
        Wend
        IE.Visible = False
        'ie is now done loading the page

End Sub

Sub UpdatePercentComplete()

    Dim ExcelSheet As Excel.Workbook
    Dim xlRange As Excel.Range
    Dim Tsk As Task

        Set ExcelSheet = GetObject("C:\Excel.xls")
        ExcelSheet.Application.Visible = True
        Set xlRange = ExcelSheet.Worksheets("FileName").Range("K2")
        ExcelSheet.Application.ScreenUpdating = False

        For Each Tsk In ActiveProject.Tasks
            If Not Tsk Is Nothing Then
                If Tsk.OutlineLevel = 1 Then
                    With xlRange
                    .Range("K2") = (Tsk.PercentComplete / 100)
                    End With
                    Set xlRange = xlRange.Offset(1,0)
                End If
            End If
        Next Tsk

        ExcelSheet.Application.ScreenUpdating = True
        ExcelSheet.Save
        ExcelSheet.Application.Quit
        Set ExcelSheet = Nothing

End Sub

Sub CostReport()

    Dim StartTime As Date

        StartTime = Now
        SaveWebData
        Do While DateDiff("s", StartTime, Now) < 15
        Loop
        UpdatePercentComplete

End Sub

The Loop in this routine gives the user enough time to click save from the
aspx file and click yes if there is an existing file with the same name.

This is a Microsoft Project newsgroup - you will probably have better
results in an Excel group

 

[Steve]

This is a Microsoft Project newsgroup - you will probably have better
results in an Excel group

Aye. The code is written in my microsoft project mpt. Its code that takes
project data and puts it into specific Excel cells. I will try it on the
Excel form as well.

 

[Rod Gill]

Okay, you need to give us more help. Firstly lets try and halve the problem
area. Just run the code with the IE part. Does this work reliably?
Just run the remaining code. Is this reliable?
What version of Project and SP level?
What version of Excel and what SP level?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Steve]

Okay, you need to give us more help. Firstly lets try and halve the problem
area. Just run the code with the IE part. Does this work reliably?
Just run the remaining code. Is this reliable?
What version of Project and SP level?
What version of Excel and what SP level?

Ok. Im using Project 2007 and Excel 2003. I've got the most up to date SPs
for both. And both parts work reliably. That is how I came to write to
seperate routines. I had originally wrote them both in the same routine. When
it wasn't working, I broke them up and tested them individually.
Individually, they both work. I am pretty sure it has something to do with IE
trying to save the Excel file and maybe not finishing the entire process
before the next routine is called.

 

[Rod Gill]

Try:

Sub UpdatePercentComplete()
Dim xlWrkBk As Excel.Workbook
Dim xlApp As Excel.Application
Dim xlRange As Excel.Range
Dim Tsk As Task
Set xlWrkBk = GetObject(, "Excel.Application")
xlApp.Visible = True
Set xlWrkBk = xlApp.ActiveWorkbook
Set xlRange = xlWrkBk.Worksheets("FileName").Range("K2")
xlApp.ScreenUpdating = False

For Each Tsk In ActiveProject.Tasks
If Not Tsk Is Nothing Then
If Tsk.OutlineLevel = 1 Then
xlRange.Range("K2") = (Tsk.PercentComplete / 100)
Set xlRange = xlRange.Offset(1, 0)
End If
End If
Next Tsk

xlApp.ScreenUpdating = True
xlWrkBk.Save
Set xlApp = Nothing
Set xlWrkBk = Nothing
End Sub



--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com