VB connection to excel spreadsheet

[gdp123]

Hi

I am trying to write a program so that the user can input some data, that data would then be placed into an excel spreadsheet. from there i want to be able to scan a certain column and any data that is below 5% accurate i want it to display in a data grid. my problem is i cant connect to the spread sheet. this is what i am trying to do, you will notice there are two types of connection. i tried both and one fails at the cn.open stage the other at the select * from stage. any help would be great, thanks in advance.

Private Sub Command1_Click()

Dim RS As Recordset
Dim cn As ADODB.Connection
Dim strConnectionString As String

Set cn = New ADODB.Connection

'strConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & App.Path & "\workbook.xls;"

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\workbook.xls;Extended Properties=""Excel 8.0;"

cn.ConnectionString = strConnectionString

cn.Open

Set RS = New Recordset
RS.CursorType = adOpenForwardOnly
RS.CursorLocation = adUseClient
RS.LockType = adLockReadOnly

RS.Open "Select * from workbook.xls", cn, adOpenStatic
RS.MoveLast
Debug.Print RS.RecordCount
RS.Close
Set RS = Nothing

End Sub

[dglienna]

Here is the place to find the right connection string.

http://connectionstrings.com/

[gdp123]

thanks i realised what i had done wrong too.

[gdp123]

ok i know have another problem, i have made the connection to the excel document. Now what i want to do is query one of the columns. i have used the select statement to do this but i dont know how to get it to select from a specific column

RS.Open "Select * from [sheet1$]", cn, adOpenStatic

i would normaly us the where statement say like

RS.Open "Select * from [sheet1$] Where (column L2:L61) = 5%", cn, adOpenStatic

I know this isnt correct but what do i need to do to be able to make this work?

(column L2:L61) is where the percentage values are kept but i also want to take all the data associated with those rows that are 5% and display them into a datagrid.

[dglienna]

This should clarify things a bit:

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
	' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

[gdp123]

thanks but i think i am over my head with this one. What part of the code will search the specific excel spread sheet column?

[dglienna]

Well, once you get this far, you'd record a macro in excel, and transfer it to vb (using the above example)

[gdp123]

i've never done that before how to you make a macro?

[dglienna]

Tools->Macro's-> Record New Macro

Do your thing(s)
Stop the recorder,

Tools->Macro's-> Edit macro to see code!

[gdp123]

i'm really having trouble with this one. i understand that when i record the macro i'm basicly acting out what i want it to do. if i the columns that i select could change everytime i run the program this could cause a problem right? as my vb program has two text boxes and a command button, the user inputs two numbers into the boxes and when the button is pressed the numbers are entered into the excel spread sheet. the spread sheet is used for calculations and then the macro is to extract the data that is 5% or below. but the numbers are not going to be the same each time. i'm sorry if i am just going round in circles. i thought that a select statement would need to be used to search the column and then retrieve the data to a datagrid.

[dglienna]

Well, it's easy to find the LAST used cell in a row or column. Once you have the macro that sets a range, you can code in the loop thru valid cells.

[dglienna]

To answer your PM:

You could use an InputBox, if you don't know which column, but if you know the first non-blank cell, you can easily find the next non-blank row or column.

[gdp123]

this realy isnt getting through to me, why would i need to find the next blank cell? what i want is to search a column in excel at runtime after i have inputed the data to be calculated. the row will change each time i run the program so i need to search the column to find the data i want. i dont seem to understand what you have told me to do and wonder if we have our wires crossed on this one. i meen for instance how do you tell vb to search collumn A row 5 and place that data into a text box. if there were multiple rows that changed at run time and say three out of five of the rows had the number 2 in and the others number 1 how can i get it to search the column and pick out the columns with the number 2 in. each time i run the program the number 2 would appear in a different row than before.

[dglienna]

Well the macro editor help says this is valid VBA:

Sub ClearRange()     
  Worksheets("Sheet1").[A1:B5].ClearContents 
End Sub  

Sub SetValue()     
  [MyRange].Value = 30 
End Sub

so, that should get you what you want if you select where range=2

[gdp123]

ok thanks, i seem to be getting somewere now. sorry its been difficult, one more question though. is it possible to have a text box in vb and the data that is inserted into. rather than being inserted into a cell in excel, is it possible ot have it store it as a variable. then i can call up the variable within the macro.