Opening Excel Problem

[jray2003]

I want to be able an Excel sheet and read the results in. I can do it when I hard code some information, but can't figure out how to allow the user to enter in the informtion.

 cdbWordProc.Filter = "EXCEL (*.xls)|*.xls"
 cdbWordProc.FilterIndex = 1
 cdbWordProc.ShowOpen
 strFileName = cdbWordProc.FileName
 Text1 = strFileName
        'Connect to Database ----->
    mstrConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\GENISYS_Roster_KieferC.xls;" & _
        "Extended Properties=Excel 8.0;"
    dcexport.ConnectionString = mstrConnectionString
    dcexport.Visible = False
    dcexport.RecordSource = "SELECT * FROM [MASTER PG 1$] "
    dcexport.Refresh

I want the user to allow them to choose the file on this line.

App.Path & "\GENISYS_Roster_KieferC.xls;"

I tried to do the following and it didn't work:
mstrConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & "Extended Properties=Excel 8.0;"
--------------------
and is there a way for them to be prompt for the workbook on this line?

SELECT * FROM [MASTER PG 1$]

Thank you..

 

[jray2003]

I got the first part working but need to know how to display the workbooks so they can choose which one to work.

--------------------
and is there a way for them to be prompt for the workbook on this line?

SELECT * FROM [MASTER PG 1$]

 

[bjd4jc]

Here is an example.

Create a new project (you can integrate later) that has a Module and Form1. Set the Startup of the project to Sub Main (in project properties). Add a listbox and a command button (List1 and Command1) to the form. Paste the following code in the form:

Public SheetName As String

Private Sub Command1_Click()
    SheetName = Me.List1.List(Me.List1.ListIndex)
    Unload Me
End Sub

Private Sub Form_Load()
    Dim con As ADODB.Connection
    Dim rsSchema As ADODB.Recordset
    
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FileName.xls;Extended Properties=Excel 8.0;"
    
    Set rsSchema = con.OpenSchema(adSchemaTables)
    
    Do While Not rsSchema.EOF
        List1.AddItem rsSchema("TABLE_NAME") & vbNullString
        rsSchema.MoveNext
    Loop
End Sub

Paste the following in a module:

Public Sub Main()
    Dim frmSelectSheet As Form1
    
    Set frmSelectSheet = New Form1
    
    frmSelectSheet.Show vbModal
    
    MsgBox frmSelectSheet.SheetName
    
    Set frmSelectSheet = Nothing
End Sub

Run the program. Look over the code to see how it works and then you can integrate it into your current project.

 

[jray2003]

Thanks for the information. I will give it a go today.

Jim

 

[jray2003]

bjd4jc

I did what you mention and I get the following error.

Dim con As ADODB.Connection

Complier Error:

User-define type not defined

Am I missing something important?

Thanks for the help and patience.

Jim

 

[bjd4jc]

You need a reference to Microsoft ActiveX Data Objects.

 

[jray2003]

I do not see "Microsoft ActiveX Data Objects". I have a ActiveX plug and that is it. I'll see if I can get it somewhere.

Thanks again

 

[johnwm]

Go to Project|References and look for Microsoft ActiveX Data Objects.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[bjd4jc]

You may download it from

http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

 

[jray2003]

Here is one last question and this might be more simple.

With this line of code :

dcexport.RecordSource = "SELECT * FROM [MASTER PG 1$] "

is there a way of just calling the first workbook?

Meaning when I open the excel spreadsheet, just select the first one no matter what the name of it is?

Thanks again for the help.

Jim

 

[bjd4jc]

I do not know of a way to specify a table name in a from clause by it's ordinal.

You would have to use my code above to get a recordset of the table names and then, instead of entering a loop you would just take the first one.

    Dim strTableName as string
    Dim con As ADODB.Connection
    Dim rsSchema As ADODB.Recordset

    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FileName.xls;Extended Properties=Excel 8.0;"
    
    Set rsSchema = con.OpenSchema(adSchemaTables)

    If Not rsSchema.EOF Then
        strTableName = rsSchema("TABLE_NAME")
        dcexport.RecordSource = "SELECT * FROM [" & strTableName & "]"
    End If

I really don't know if this will produce consistent results or not. You can give a try. Maybe someone else here can provide a way to select * from a table using it's ordinal rather than its name...

 

[johnwm]

I believe you can reference the Worksheets collection. The first Worksheet would be :
ThisWorkbook.Worksheets(1)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[jray2003]

Dang, I am such an idiot. I know what I was doing wrong....Time for a vacation..

Thanks again...

 

[bjd4jc]

What is it that you were doing wrong?

 

[jray2003]

I missed you message about "Go to Project|References and look for Microsoft ActiveX Data Objects." I just saw the one about the file download.

What I have done is create the list box allow the user to select the table. Now what I have to do is once the table is selected, load and display the workbook.

Currently I have code that does that as you can see and need to figure out how to modify the code with your outstanding advice.

Jim