Connection string problem using a combobox

[Zick Technology]

Hello i have a problem on connecting my database.
i have this kind of code which would get the item in the combobox and use it as my connection

Dim ConFunnelLocation As String = "C:\Database\Funnel\" & Form_Funnel_Report.Combo_Funnel_EBU.Text & ".xls"

and a table

Dim TblFunnelCon As String = Form_Funnel_Report.Combo_Funnel_AM.Text & "$"

so this could be my code.

con.ConnectionString = ("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & ConFunnelLocation & "';Extended Properties=Excel 8.0;")
    con.Open()
    dt.Clear()

    ds.Tables.Add(dt)
    Try

        With Form_Funnel_Report

            Dim da As New OleDbDataAdapter("Select * from [" & TblFunnelCon & "]", con)
            da.Fill(dt)

But it cannot open my database.

unless i change my declaration to the specific name of my Table and my Excel file like this

Dim ConFunnelLocation As String =  "C:\Database\Funnel\EBU 1.xls"
Dim TblFunnelCon As String = "Sheryl Manuel$"

this work fine but i need to use combobox so it will lessen my code and it can be more flexible.

please help me. thank you in advanced.

[tinstaafl]

set a break point and check the values of the strings.

[G_Waddell]

Hi,

Try doing something like this:

Dim ConFunnelLocation As String

If trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) <> "" then
    ConFunnelLocation = "C:\Database\Funnel\" & trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) & ".xls"
else
     msgbox("Please select the excel sheet")
     exit sub
end if

[Begginnerdev]

I think your problem may be just as George and tins have stated.

I think you are simply passing in a string that may have an extra space or a character that's not expected.

Step through and look at the autos to see what is happening with your code.

[G_Waddell]

Just occurred to me, if there's a space in the filename you may have to do this:

Dim ConFunnelLocation As String

If trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) <> "" then
    ConFunnelLocation = "C:\Database\Funnel\[" & trim(Form_Funnel_Report.Combo_Funnel_EBU.Text) & "].xls"
else
     msgbox("Please select the excel sheet")
     exit sub
end if

ACTUALLY FORGET that - rush of blood to the head!!