Excel Object problems

[meg99]

using Proj2003 and Excel2003

I need to open an Excel file and read the contents, but this code
fails.
Dim xlapp as Excel.application
Dim XLfile as string

Set xlapp = CreateObject("Excel.application")
XLfile = ActiveProject.Path & "\NonWorking Dates.xls"
xlapp.GetOpenFilename (XLfile)

The error message I get is "Method 'GetOpenFilename Oject' failed"


What have I done wrong? Is there a better way to do this?


meg99

 

[JulieS]

using Proj2003 and Excel2003

I need to open an Excel file and read the contents, but this code
fails.
Dim xlapp as Excel.application
Dim XLfile as string

Set xlapp = CreateObject("Excel.application")
XLfile = ActiveProject.Path & "\NonWorking Dates.xls"
xlapp.GetOpenFilename (XLfile)

The error message I get is "Method 'GetOpenFilename Oject' failed"


What have I done wrong? Is there a better way to do this?


meg99

Hello meg99,

I believe you need
xlApp.Workbooks.Open XLFile

From help the GetOpenFilename method displays the Open dialog box and
would allow a user to select a file.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional information
about Microsoft Project

 

[John]

using Proj2003 and Excel2003

I need to open an Excel file and read the contents, but this code
fails.
Dim xlapp as Excel.application
Dim XLfile as string

Set xlapp = CreateObject("Excel.application")
XLfile = ActiveProject.Path & "\NonWorking Dates.xls"
xlapp.GetOpenFilename (XLfile)

The error message I get is "Method 'GetOpenFilename Oject' failed"


What have I done wrong? Is there a better way to do this?


meg99

Meg99,
The GetOpenFIlename Method basically calls up the File/Open dialogue. It
does NOT open any files. You have specified the "XLfile" as an argument
and that argument is not applicable to the method, hence the error.

However, what exactly is your end goal? How do you want to read the
Excel file into Project? You do need to start Excel, unless it is
already open, then the code sequence should be more like the following,

dim xl as Excel.application
On Error Resume Next
Set xl = GetObject(, "Excel.application")
If Err <> 0 Then
On Error GoTo 0
Set xl = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Excel application is not available on this workstation" _
& Chr(13) & "Install Excel or check network connection",
vbCritical, _
MsgBxTitle & " - Fatal Error"
Set xl = Nothing
Exit Sub
End If
End If
On Error GoTo 0 'clear error function

xl.Workbooks.Add
BookNam = xl.ActiveWorkbook.Name

Since you already know the Excel filename, you do NOT need to pop up the
Open dialogue. All you need to do is to pull the Excel data into the
appropriate fields of Project and the code to do that can take many
different forms.

John
Project MVP

 

[meg99]

Meg99,
The GetOpenFIlename Method basically calls up the File/Open dialogue. It
does NOT open any files. You have specified the "XLfile" as an argument
and that argument is not applicable to the method, hence the error.

However, what exactly is your end goal? How do you want to read the
Excel file into Project? You do need to start Excel, unless it is
already open, then the code sequence should be more like the following,

dim xl as Excel.application
On Error Resume Next
Set xl = GetObject(, "Excel.application")
If Err <> 0 Then
    On Error GoTo 0
    Set xl = CreateObject("Excel.Application")
    If Err <> 0 Then
        MsgBox "Excel application is not available on this workstation" _
            & Chr(13) & "Install Excel or check network connection",
vbCritical, _
            MsgBxTitle & " - Fatal Error"
        Set xl = Nothing
        Exit Sub
    End If
End If
On Error GoTo 0     'clear error function

xl.Workbooks.Add
BookNam = xl.ActiveWorkbook.Name

Since you already know the Excel filename, you do NOT need to pop up the
Open dialogue. All you need to do is to pull the Excel data into the
appropriate fields of Project and the code to do that can take many
different forms.

John
Project MVP- Hide quoted text -

- Show quoted text -

John,
I have another user form that has a Listbox that contains a list. I
can populate the list from a text file. However, that takes an extra
step to export the Exel data to a text file. I want to eliminate that
step and read the data directly from Excel - 3 columns and about 30
rows.

Basically the form allows the user to update selected calendars with
imported holiday information. The excel file contains all the basic
holidays. The user selects the year (in the Project User form) and
that data is piped to the excel file. A macro in Excel calculates the
holiday date (5th Monday in May for May Day, 4th Thursday in November
for Thanksgiving etc). The resulting holiday list is imported into
the listbox that allows the user to determine if the holiday is
working or nonworking. When the user is finished, he can update the
selected calendar.

So...to reiterate, I want to open Excel from Project, push the
calendar year into the appropriate cell, read the resulting data from
A2:C30 (or last row), populate the listbox, close Excel.

meg99

 

[John]

John,
I have another user form that has a Listbox that contains a list. I
can populate the list from a text file. However, that takes an extra
step to export the Exel data to a text file. I want to eliminate that
step and read the data directly from Excel - 3 columns and about 30
rows.

Basically the form allows the user to update selected calendars with
imported holiday information. The excel file contains all the basic
holidays. The user selects the year (in the Project User form) and
that data is piped to the excel file. A macro in Excel calculates the
holiday date (5th Monday in May for May Day, 4th Thursday in November
for Thanksgiving etc). The resulting holiday list is imported into
the listbox that allows the user to determine if the holiday is
working or nonworking. When the user is finished, he can update the
selected calendar.

So...to reiterate, I want to open Excel from Project, push the
calendar year into the appropriate cell, read the resulting data from
A2:C30 (or last row), populate the listbox, close Excel.

meg99

meg99,
Ok, that's quite a process you are using but it sounds like it could be
simplified. Help me understand some of the details.
1. Does the user select custom Project calendars as well as a particular
year on the userform? If not, what all does the user select on the
userform?
2. Who creates the holiday data for the Excel Workbook (i.e. you or
someone at corporate level)?
3. What format is the data in Excel? For example, you apparently have 3
columns and 29 rows of data, what are the rows and columns?
4. Where does your userform reside (i.e. in a Project VBA macro, Excel
VBA macro, or perhaps in an independent VB application)?
5. What version of Project are you using (including updates)?
6. What version of Excel (i.e. Office) are you using?
7. What Windows version are you using?

There certainly doesn't seem to be a reason for an intermediate
conversion to a text file and there may not even be a need for a
userform (depending on the answer to question 1 above). What you want to
do is certainly doable but the details may require some one-on-one help.
Let's start with the above and see where that leads.

John
Project MVP

 

[meg99]

meg99,
Ok, that's quite a process you are using but it sounds like it could be
simplified. Help me understand some of the details.
1. Does the user select custom Project calendars as well as a particular
year on the userform? If not, what all does the user select on the
userform?
2. Who creates the holiday data for the Excel Workbook (i.e. you or
someone at corporate level)?
3. What format is the data in Excel? For example, you apparently have 3
columns and 29 rows of data, what are the rows and columns?
4. Where does your userform reside (i.e. in a Project VBA macro, Excel
VBA macro, or perhaps in an independent VB application)?
5. What version of Project are you using (including updates)?
6. What version of Excel (i.e. Office) are you using?
7. What Windows version are you using?

There certainly doesn't seem to be a reason for an intermediate
conversion to a text file and there may not even be a need for a
userform (depending on the answer to question 1 above). What you want to
do is certainly doable but the details may require some one-on-one help.
Let's start with the above and see where that leads.

John
Project MVP- Hide quoted text -

- Show quoted text -

John,
I solved the problem. However, to answer your questions:
1. Does the user select custom Project calendars as well as a
particular
year on the userform? If not, what all does the user select on the
userform?
The user picks the calendar from a combobox and enters the year
in a text box

2. Who creates the holiday data for the Excel Workbook (i.e. you or
someone at corporate level)?
I created the holiday list from national and corporate listings
(ie

What format is the data in Excel? For example, you apparently have 3
columns and 29 rows of data, what are the rows and columns?
row 1 is the name of the holiday
row 2 is the calculated date based on the year
row 3 is titled "Work" and is "Yes" or "No" adjustable by user
after inport (ie New Years Day is "No", while MLK is usually "Yes")

4. Where does your userform reside (i.e. in a Project VBA macro,
Excel
VBA macro, or perhaps in an independent VB application)?
The user form is in Project and is started from a user menu.
The macros inside the form open Excel and populate the listbox.
The default calendar is Standar and the default year is the
current year. The user can change these and update the list prior to
updating the calendar.
The dates in Excel are calculated by cell formulas based on
the entered year.
5. What version of Project are you using (including updates)?
2003 SP3
What version of Excel (i.e. Office) are you using?
2003 SP2
7. What Windows version are you using?
XP Pro SP2

Thanks for the help. You put me on the right track

meg99

 

[John]

John,
I solved the problem. However, to answer your questions:
1. Does the user select custom Project calendars as well as a
particular
year on the userform? If not, what all does the user select on the
userform?
The user picks the calendar from a combobox and enters the year
in a text box

2. Who creates the holiday data for the Excel Workbook (i.e. you or
someone at corporate level)?
I created the holiday list from national and corporate listings
(ie

What format is the data in Excel? For example, you apparently have 3
columns and 29 rows of data, what are the rows and columns?
row 1 is the name of the holiday
row 2 is the calculated date based on the year
row 3 is titled "Work" and is "Yes" or "No" adjustable by user
after inport (ie New Years Day is "No", while MLK is usually "Yes")

4. Where does your userform reside (i.e. in a Project VBA macro,
Excel
VBA macro, or perhaps in an independent VB application)?
The user form is in Project and is started from a user menu.
The macros inside the form open Excel and populate the listbox.
The default calendar is Standar and the default year is the
current year. The user can change these and update the list prior to
updating the calendar.
The dates in Excel are calculated by cell formulas based on
the entered year.
5. What version of Project are you using (including updates)?
2003 SP3
What version of Excel (i.e. Office) are you using?
2003 SP2
7. What Windows version are you using?
XP Pro SP2

Thanks for the help. You put me on the right track

meg99

meg99,
You're welcome and thanks for the feedback.
John