-
July 15th, 2007, 06:20 PM
#1
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
-
July 15th, 2007, 08:02 PM
#2
Re: VB connection to excel spreadsheet
Here is the place to find the right connection string.
http://connectionstrings.com/
-
July 16th, 2007, 12:48 PM
#3
Re: VB connection to excel spreadsheet
thanks i realised what i had done wrong too.
-
July 16th, 2007, 06:14 PM
#4
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.
-
July 16th, 2007, 07:01 PM
#5
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
-
July 17th, 2007, 01:06 PM
#6
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?
-
July 17th, 2007, 01:26 PM
#7
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)
-
July 17th, 2007, 01:29 PM
#8
Re: VB connection to excel spreadsheet
i've never done that before how to you make a macro?
-
July 17th, 2007, 05:54 PM
#9
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!
-
July 17th, 2007, 06:24 PM
#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.
-
July 17th, 2007, 07:32 PM
#11
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.
-
July 18th, 2007, 10:29 AM
#12
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.
-
July 20th, 2007, 04:57 PM
#13
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.
-
July 20th, 2007, 08:19 PM
#14
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
-
July 22nd, 2007, 06:42 AM
#15
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|