Open excel file from Ms Project

[WildWally]

I'm back for more help. I did however purchase a book from Rod. I just
can't wait for the book to arrive to get this last bit to work.

Here is what I got.

I have a userform as mentioned in a previous post, with 5 textboxes. The
five textboxes can be manually filled or import the data from the said excel
file. I tried Application.Fileopen but it tries to use a import wizard. I
don't want the wizard to run, I want the data within the 5 cells in my excel
sheet to be pasted in essencse to the textbox(s). Then when the user hits
the enter button they are transposed into the schedule.


And Jack if you are reading this I took your advice as to not use the cell
but instead the task(s) name to find the cell. Thanks it worked like a charm.

 

[Jack Dahlgren]

You need to add a reference to the excel library and open the file using
Excel.

something like this:
'open an instance of Excel
set xlApp as new excel.application
'use the excel version of fileopen
xlApp.Workbooks.Open "C:\myfile.xls"

of course you could also browse for the file by using the file dialog box.

-Jack

 

[John]

I'm back for more help. I did however purchase a book from Rod. I just
can't wait for the book to arrive to get this last bit to work.

Here is what I got.

I have a userform as mentioned in a previous post, with 5 textboxes. The
five textboxes can be manually filled or import the data from the said excel
file. I tried Application.Fileopen but it tries to use a import wizard. I
don't want the wizard to run, I want the data within the 5 cells in my excel
sheet to be pasted in essencse to the textbox(s). Then when the user hits
the enter button they are transposed into the schedule.


And Jack if you are reading this I took your advice as to not use the cell
but instead the task(s) name to find the cell. Thanks it worked like a charm.

WildWally,
Try GetObject or CreateObject to open the Excel file. I usually use the
following sequence,

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

Hope this helps.
John
Project MVP

 

[WildWally]

Ok tell me what I'm foing wrong here.

I have used this code in excel to open the file browser for the user to pick
which file but I have something wrong here.

Private Sub importhoursbutton_Click()
Dim xlApp As Object

Dim sfilename As String

'open an instance of Excel
Set xlApp = New Excel.Application
'use the excel version of fileopen
Set filename = Application.GetOpenFileName
xlApp.Workbooks.Open filename:=sfilename, UpdateLinks:=0, ReadOnly:=True
Workbooks(Workbooks.Count).Sheets("Main").Activate
If Range("L32") = "Ver Date: OCT ' 07" Then
xlApp.Workbook.Worksheet("BaseBid").Visible = False
hoursenter.PMhoursTB.Value = Worksheets("BaseBid").Range("K27")
hoursenter.SafetyhoursTB.Value =
Worksheets("BaseBid").Range("K28")
hoursenter.MischoursTB.Value = Worksheets("BaseBid").Range("K29")
hoursenter.SafetyhoursTB.Value =
Worksheets("BaseBid").Range("K30")
hoursenter.MischoursTB.Value = Worksheets("BaseBid").Range("K31")
Else
Application.DisplayAlerts = False
Workbooks(Workbooks.Count).Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
hoursenter.Hide
finalestimateerror.Show
End If
End Sub

 

[WildWally]

Some of that post didn't go right as I was editing it it posted. Here is the
exact code from my excel file that works like I want it to.


Private Sub CommandButton3_Click()
Dim sFileName As String
Dim WB As Workbook
Dim OB As Workbook

Set WB = ThisWorkbook

'Show the open dialog and pass the selected _
file name to the String variable "sFileName"

Filename = Application.GetOpenFilename
'They have cancelled.
If Filename = "False" Then Exit Sub
Application.ScreenUpdating = False
Workbooks.Open Filename:=Filename, UpdateLinks:=0, ReadOnly:=True
Workbooks(Workbooks.Count).Sheets("Main").Activate
If Range("L32") = "Ver Date: OCT ' 07" Then
Workbooks(Workbooks.Count).Worksheets("TC-11").Activate
UserForm1.TextBox1.Value = Worksheets("TC-11").Range("K27")
UserForm1.TextBox2.Value = Worksheets("TC-11").Range("K28")
UserForm1.TextBox3.Value = Worksheets("TC-11").Range("K29")
UserForm1.TextBox4.Value = Worksheets("TC-11").Range("K30")
UserForm1.TextBox5.Value = Worksheets("TC-11").Range("K31")
UserForm1.TextBox6.Value = Worksheets("TC-11").Range("K32")
UserForm1.TextBox7.Value = Worksheets("TC-11").Range("G29")
UserForm1.TextBox8.Value = Worksheets("TC-11").Range("G30")
UserForm1.TextBox9.Value = Worksheets("TC-11").Range("G31")
UserForm1.TextBox10.Value = Worksheets("TC-11").Range("G32")
Application.DisplayAlerts = False
Workbooks(Workbooks.Count).Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
WB.Worksheets("Main").Activate
Else
Application.DisplayAlerts = False
Workbooks(Workbooks.Count).Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
UserForm1.Hide
UserForm6.Show
End If

End Sub

 

[Alan]

Some of that post didn't go right as I was editing it it posted.  Here is the
exact code from myexcelfile that works like I want it to.

Private Sub CommandButton3_Click()
Dim sFileName As String
Dim WB As Workbook
Dim OB As Workbook

Set WB = ThisWorkbook

'Show theopendialog and pass the selected _
file name to the String variable "sFileName"

Filename = Application.GetOpenFilename
'They have cancelled.
If Filename = "False" Then Exit Sub
Application.ScreenUpdating = False
Workbooks.OpenFilename:=Filename, UpdateLinks:=0, ReadOnly:=True
Workbooks(Workbooks.Count).Sheets("Main").Activate
If Range("L32") = "Ver Date: OCT ' 07" Then
    Workbooks(Workbooks.Count).Worksheets("TC-11").Activate
    UserForm1.TextBox1.Value = Worksheets("TC-11").Range("K27")
    UserForm1.TextBox2.Value = Worksheets("TC-11").Range("K28")
    UserForm1.TextBox3.Value = Worksheets("TC-11").Range("K29")
    UserForm1.TextBox4.Value = Worksheets("TC-11").Range("K30")
    UserForm1.TextBox5.Value = Worksheets("TC-11").Range("K31")
    UserForm1.TextBox6.Value = Worksheets("TC-11").Range("K32")
    UserForm1.TextBox7.Value = Worksheets("TC-11").Range("G29")
    UserForm1.TextBox8.Value = Worksheets("TC-11").Range("G30")
    UserForm1.TextBox9.Value = Worksheets("TC-11").Range("G31")
    UserForm1.TextBox10.Value = Worksheets("TC-11").Range("G32")
    Application.DisplayAlerts = False
    Workbooks(Workbooks.Count).Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    WB.Worksheets("Main").Activate
Else
    Application.DisplayAlerts = False
    Workbooks(Workbooks.Count).Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    UserForm1.Hide
    UserForm6.Show
End If

End Sub










- Show quoted text -

Hi,

I used to have similar problems, but the file is damaged. If your file
is also the same, you may try Advanced Excel Repair at http://www.datanumen.com/aer/
This tool is rather useful in salvaging damaged Excel xls files. Hope
this helps.

Alan