Results 1 to 3 of 3

Thread: Could not find Installable ISAM when trying to load in an Excel Spreadsheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    8

    Could not find Installable ISAM when trying to load in an Excel Spreadsheet

    I am trying to load in a spread sheet for a user to quickly look up info without needing to keep the spreadsheet open.
    I've looked through several different threads on this and nothing seems to help me. Maybe someone can spot the issue i'm missing?

    Code:
    Imports Microsoft.Office.Interop
    Public Class Form1
        Dim conn As OleDb.OleDbConnection
        Dim dta As OleDb.OleDbDataAdapter
        Dim dts As DataSet
        Dim excel As String
        Dim openFile As New OpenFileDialog
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Try
    
                openFile.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
                'openFile.filter = "All Files (*.*)|*.*|Excel Files (*.xlsx)|*.xlsx|Xls Files (*.xls)|*.xls"
                If openFile.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK Then
                    Dim fi As New IO.FileInfo(openFile.FileName)
                    Dim filename As String = openFile.FileName
                    excel = fi.FullName
                    conn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.16.0; Data Source=" + excel + ";Extended Properties = Excel 16.0 Xml;HDR=NO;IMEX=1")
                    dta = New OleDb.OleDbDataAdapter("select * From [Current orders$]", conn)
                    dts = New DataSet
                    dta.Fill(dts, "Current orders$")
                    dgvExcelInfo.DataSource = dts
                    dgvExcelInfo.DataMember = "[Current orders$]"
                    conn.Close()
                End If
    
            Catch ex As Exception
                MsgBox(ex.Message)
                Exit Sub
            End Try
    
    
        End Sub
    when i run the above code, the form starts to load and the dialogbox pops up asking the user to select the spreadsheet to use(this will be taken out sense this will only be made for one spreadsheet but just using for now as a debugging purpose) and i get the "Could not find installable ISAM" with just an OK button that just shuts everything down.

    I appreciate any that is given.

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Could not find Installable ISAM when trying to load in an Excel Spreadsheet

    You should try searching this site. That has been asked many times here. The ones I looked at focused on connection strings:

    http://www.vbforums.com/showthread.p...able-ISAM-quot
    Please remember next time...elections matter!

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Could not find Installable ISAM when trying to load in an Excel Spreadsheet

    try it this way....
    Code:
    Option Strict On
    Imports System.Data.OleDb
    
    
    
    Public Class Form1
    
        Private objConnection As OleDbConnection
        Private objCommand As OleDbCommand
        Private objDataAdapter As OleDbDataAdapter
        Private objDataTable As System.Data.DataTable
    
        Private PathExcelFile As String = "E:\Book1.xlsx"
        Private strConnectionString As String = _
        "provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & PathExcelFile & ";" _
        & "Extended Properties=""Excel 12.0 XML;HDR=Yes"""
    
    
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            objConnection = New OleDbConnection(strConnectionString)
    
            'select sheet
            objCommand = New OleDbCommand("SELECT * FROM [Sheet4$]", objConnection)
    
            'select Range
            'objCommand = New OleDbCommand("SELECT * FROM [Sheet1$C6:K10]", objConnection)
    
            'select with Letter, AD1 and AD2 are Header names in the Excel Sheet
            'objCommand = New OleDbCommand("SELECT AD1, AD2 FROM [Sheet1$] Where AD2 Like 'Q%'", objConnection)
    
            objDataAdapter = New OleDbDataAdapter
            objDataAdapter.SelectCommand = objCommand
            objDataTable = New System.Data.DataTable
            objDataAdapter.Fill(objDataTable)
    
            DataGridView1.DataSource = objDataTable.DefaultView
            'Clean(up)
            objDataAdapter.Dispose()
            objDataAdapter = Nothing
            objCommand.Dispose()
            objCommand = Nothing
            objConnection.Dispose()
            objConnection = Nothing
        End Sub
    
    End Class
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width