Reading Excel Cell Values

[jep2002]

Dear all,

I have made a function to open a excel file and read the content of a column..The thing is...it works well when i create an dummy excel file...but it failed when i want to read from an existing file which contain macro functions...it sort of captures the value but it returns null ( i think it was because of the macro had not finish/started filling up the cells)..below are my code. Please help me..thanks

Try 
xlApp = New Excel.Application 
xlApp.DisplayAlerts = False 
xlApp.ScreenUpdating = False 
xlApp.Workbooks.Open("c:\Data.xls") 
xlApp.Visible = True 
xlSht = xlApp.Sheets(1) 
 
Dim dsNewRow As DataRow 
xlRng = xlSht.Cells(9, 1) 
dsNewRow = ds.Tables("LINE_NUMBER").NewRow() 
dsNewRow.Item("LINE_NUMBER") = xlRng.Value 
ds.Tables("LINE_NUMBER").Rows.Add(dsNewRow) 
 
dgLineNumber.Refresh() 
 
Catch ex As Exception 
MsgBox(ex.ToString) 
 
End Try

 

[XL-Dennis]

Hi John,

Since You have macros in the workbook the security messagebox usually pop up when opening the workbook. We can send keystrokes to Excel to close it but that may or may not work .

The best approach is simple to use a classic ADO approach where we handle the workbook as a "database":

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button2_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button2.Click[/SIZE]
[SIZE=2][COLOR=#008000]'Unless You use late binding a reference to the following external library is necessary:[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'Microsoft ActiveX Data Object x.x where x.x = version in use.[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'The connection string.[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'If no column names exist then the parameter HDR's value must be No.[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'8.0 does not refer to the Excel version in use![/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Const[/COLOR][/SIZE][SIZE=2] stCon [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#a31515]"Provider=Microsoft.Jet.OLEDB.4.0;"[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#a31515]"Data Source=c:\XLDennis.xls;"[/COLOR][/SIZE][SIZE=2] & _[/SIZE]
[SIZE=2][COLOR=#a31515]"Extended Properties=""Excel 8.0;HDR=No"";"[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'The SQL expression.[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'If we only want to retrieve one cell's value then we need to[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'refer to it as the following do:[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Const[/COLOR][/SIZE][SIZE=2] stSQL [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#a31515]"SELECT * FROM [Sheet1$B3:B3]"[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ADODB.Connection[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rst [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ADODB.Recordset[/SIZE]
[SIZE=2]cnt = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] ADODB.Connection[/SIZE]
[SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE][SIZE=2] cnt[/SIZE]
[SIZE=2].Open(stCon)[/SIZE]
[SIZE=2]rst = .Execute(stSQL)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]With[/COLOR][/SIZE]
[SIZE=2]MsgBox(rst.Fields(0).Value)[/SIZE]
[SIZE=2]rst.Close()[/SIZE]
[SIZE=2]cnt.Close()[/SIZE]
[SIZE=2]rst = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]cnt = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]