dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: VB connection to excel spreadsheet

  1. #1
    Join Date
    Jul 2007
    Posts
    10

    VB connection to excel spreadsheet

    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

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

    Here is the place to find the right connection string.

    http://connectionstrings.com/
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

    thanks i realised what i had done wrong too.

  4. #4
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

    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.

  5. #5
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

    This should clarify things a bit:

    Code:
    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
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  6. #6
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

    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?

  7. #7
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  8. #8
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

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

  9. #9
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

    Tools->Macro's-> Record New Macro

    Do your thing(s)
    Stop the recorder,

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  10. #10
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

    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.

  11. #11
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  12. #12
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  13. #13
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

    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.

  14. #14
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: VB connection to excel spreadsheet

    Well the macro editor help says this is valid VBA:
    Code:
    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
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  15. #15
    Join Date
    Jul 2007
    Posts
    10

    Re: VB connection to excel spreadsheet

    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.

Page 1 of 2 12 LastLast

Posting Permissions

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


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)