Excel连接字符串出错（HDR =是） [英] Error with Excel Connection String (HDR=Yes)

问题描述

嗨伙计们，



我需要帮助，了解为什么我的Excelconnection字符串会出现此错误的原因，当我设置HDR =是时，我一直遇到d错误但是HDR =否，它返回F1，F2，......：



错误：对象引用未设置为实例一个物体？

hi guys,

I need help in understanding what could possibly be the reason why am getting this error with my Excelconnection string, when i set HDR=Yes, i keep encountering d error below but with HDR=No, it return F1, F2, ......:

Error: Object reference not set to an instance of an object?

Dim XConn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & UZO.FileName & ";" & " Extended Properties=Excel 8.0;Imex=2;HDR=Yes")

请帮忙，因为我需要它返回第1行作为标题/列而不是F1，F2 .....



Thanx家伙事先

Kindly help out coz i need it to return d 1st row as the headers/column and not F1, F2 .....

Thanx guys in advance

推荐答案

这行代码不可能产生错误。发生错误的代码在哪里？



您运行的是哪个版本的Windows？ 32位还是64位？哪个版本的Office？



您发布的一行代码中的第一个问题是扩展属性值必须用引号括起来，如下所示： br />

That line of code can't possibly generate the error. Where is the code where the error does occur?

What version of Windows are you running? 32 or 64 bit? Which version of Office?

The first problem in the one line of code that you DID post is the Extended Properties value has to be enclosed in quotes, like this:

Dim xConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & UZO.FileName & ";Extended Properties=""Excel 8.0;Imex=2;HDR=Yes""")

您好Dave Kreskowiak，



Windows 7,32位，MS Office 2007在我的系统中但是用于此目的的Excel是来自美国Webtma公司的模板，设计为数据库文件并且是MS Excel 1997-2003。 />


Thanx回复。错误并没有指向任何特定的代码行，而是每当我运行d程序并设置HDR = YES时，它只是带出d错误并返回空白输出。



我尝试了不同的方法，我甚至为连接字符串创建了一个函数，但仍然没有为wotking。



我的代码如下：

Hi Dave Kreskowiak,

Windows 7, 32 bit, MS Office 2007 in my system but the Excel am using for this purpose is a template from Webtma, USA company designed as a database file and is MS Excel 1997-2003.

Thanx for the reply. The error doesn't point to any particular line of code instead whenever i run d program and set HDR=YES, it's just brought out d error and return blank output.

I've tried different ways, i even created a function for the connection string, but still not wotking.

My codes below:

<pre lang="vb">Private _blnMixedData As Boolean = True
    Private _blnHeaders As Boolean = True</pre>

Private Function ExcelConnection() As String
            Return "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & SVTest & ";" & "Extended Properties=" & Convert.ToChar(34).ToString() & "Excel 8.0;" & "HDR=YES;" & "Imex=2;" & Convert.ToChar(34).ToString()
        End Function

Private Function ExcelConnection() As String
           Return "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & SVTest & ";" & "Extended Properties=" & Convert.ToChar(34).ToString() & "Excel 8.0;" & ExcelConnectionOptions() & Convert.ToChar(34).ToString()
       End Function

Private Function ExcelConnectionOptions() As String
            Dim strOpts As String = ""
            If Me.MixedData = True Then
                strOpts += "Imex=2;"
            End If
            If Me.Headers = True Then
                strOpts += "HDR=Yes;"
            Else
                strOpts += "HDR=No;"
            End If
            Return strOpts
        End Function

Private Sub RetrieveSheetnames()
       Try
           Me.ComboBox1.Items.Clear()

           If _exr IsNot Nothing Then
               _exr.Dispose()
               _exr = Nothing
           End If

           _exr = New ExcelReader()
           _exr.ExcelFilename = ExcelFilename
           _exr.Headers = True
           _exr.MixedData = True
           Dim sheetnames As String() = Me._exr.GetExcelSheetNames()
           Me.ComboBox1.Items.AddRange(sheetnames)
       Catch ex As Exception
           MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
       End Try
   End Sub

非常感谢您的帮助。



非常感谢

Your kind assistance would be highly appreciated.

Thanks alot

看看此处 [ ^ ]并详细了解HDR和IMEX选项。

Excel版本可用此处 [ ^ ]。

Take a look at here[^] and read more about HDR and IMEX options.
Excel versions are available here[^].

这篇关于Excel连接字符串出错（HDR =是）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！