Visual Basic :: Problem Using An ADO Data Control And An ADO Datagrid In Excel.

Trying To Export Data From Datagrid To Excel
Hello all,

I know this question has been asked before, but I think my situation is a little different.

I have attached my code so that it will be easier for you to see what I mean.

In my project I have a Query form and in this form I have a lstbox setup so that when the user clicks on one of the items, then it will display the results of the query into a datagrid. This all works just fine.

The problem is: I want the the current query that is displayed in the datagrid to be exported to excel so the user can print it out

and then the user will do the next query and then print out the datagrid results of that one and so on unitl all have been printed.

Any help would be greatly appreciated.

Oh, by the way, I used Si the Geeks tutorial and I was able to open the excel worksheet, but thats about it.

How To Copy DataGrid Data To Excel?
i want to give facility to my user that they can copy content of Datagrid data and pest it to MS Excel is it possible or not

Copy Excel Data Into A Datagrid
Copy Excel Data Into a Datagrid ...?

Import Excel Data Into A VB DataGrid
Can anyone tell me how to import data from an Excel spreadsheet into a VB datagrid?

Copy The Data In Excel To Datagrid
hi~

I would like to know if there is any method to copy the data in excel to datagrid by VB 6.0.

Do you know??

Moreover,
I have heard about an indirect method:
firstly, copy the excel's data to MS access
then, copy the data in Access to the datagrid.

Is it the only way to do the task???

Thanks for your help~

Datagrid && ADO Data Control
Newbie question. I have created a Datagrid control bound to an ADO Data Control using a particular database account and password at design time. How do I use a different account and password at run time? The datagrid control apparently wants to connect first using the design time account/password before I can change the connection using code. In other languages, data sources can be disconnected at design time. I don't see how this is done with an ADO control.

Any help greatly appreciated.

Datagrid: No Ado Data Control
I am trying to populate a datagrid control without having to bind it to an ado data control.

the data, however, is not being displayed. The recordset is retrieving the records from my database but nothing is being displayed in my datagrid.

Here is my code
dim rsS as adodb.recordset
set rsS=new adodb.recordset

rsS.CursorLocation = adUseClient
rsS.CursorType = adOpenStatic
rsS.LockType = adLockOptimistic
rsS.Open "Select School_ID,School_Name,Address,Telephone from School Order by School_Name", connection

Set dgSchools.DataSource = rsS

Do I have to loop through the recordset and set the datafield property of the datagrid columns to the fields in the recordset?

I thought that by setting the datasource property that would be automatically done.

Tesita

Datagrid & ADo Data Control
I have a lookup window that uses datagrid and ADO data control as the data source. After compiling my application that uses this lookup, and deploy it to the target machine, the lookup window on popup does not display data.

However, when i installed VB6 Disk 1 on the target machine and run my application, the lookup window works.

pls how can i figure out the missing file in my application?

thanks

Export Data From Vb Datagrid To Excel Worksheet
i have this problem that i want to export my data of
datagrid to excel worksheet and i use the following code
i just write this code to load event of the form
firstly consider the code
Private Sub Form_Load()
Dim excel_app As New Excel.Application
Dim excel_sheet As New Excel.Worksheet
Dim row As Long
Dim rs As New ADODB.Recordset
Dim statement As String
Dim i As Integer
    Set excel_app = CreateObject("Excel.Application")
    excel_app.Visible = True
    rs.CursorLocation = adUseClient

    rs.Open "select * from weeklyitem", db, adOpenDynamic, adLockOptimistic
    ' MsgBox rs.Fields(1)

    excel_sheet.Cells(1, 1) = rs.Fields(1).Value
'  For i = 1 To rs.Fields.Count - 1
'        excel_sheet.Cells(1, i) = rs.Fields(i).Name
'        Next i
'      row = 2
'    Do While Not rs.EOF
'        For i = 1 To rs.Fields.Count - 1
'            excel_sheet.Cells(row, i) = rs.Fields(i).Value
'        Next i
'
'        row = row + 1
'        rs.MoveNext
'    Loop
rs.Close
End Sub

now sir i have see a error msg
that "class does not support automation or does not
support expected interface"

plz tell me what i do

How To Export Specified Excel Data Range To VB.NET Datagrid
Hi,

I would like to export specified Excel Data range to VB.NET Datagrid, not all data in the Excel data is required. The required excel data range is defined by two datetimepicker, defining which excel cell data are to be exported to Datagrid. Not sure how to work with Excel and Datagrid, hope dudes and guru can help with this problem...Thanks

Edited by - roniwu on 11/11/2005 8:22:33 AM

Datagrid,recordset % Ado Data Control
hi, i have a problem with datagrids.

i want to view the result of a query into a datagrid.
i created a recordset(rs) containing the data.
so, i added an ado data control(adodc1) , configured the connection and i wrote:

adodc1. recordsource =rs

after i wrote

set datagrid1.datasource=adodc1

but in the datagrid only the FIRST row of the recordset is shown?
why?

thanks

Clear Data From Datagrid Control?
can anyone help me with this...please..

how do I clear contents of datagrid control? thanks in advance...

How To Display Data In DataGrid Control
please tell me how to display data in DataGrid control, the simplest way to do it. please this is very urgent to me

Inserting Data Using DataGrid Control
On a form, I have a combo box and a data grid.

Based on user's select in the combo box, data grid displays certain data which is supplied by stored procedure.

ComboBox On_LostFocus()
dataGrid.dataSource = rsStoredProc
end ComboBoxSub

Thats how my dataGrid gets it data. I have set the AllowUpdates, AllowInsert, AllowDelete properties of the dataGrid to TRUE. But still there is no additional row on the dataGrid for the user to add data, neither can the user edit or delete.

What is wrong here. Please suggest.

thank you in advance!
Ankeet

DataGrid And ADO Data Control PROBLEMS!
Hi,

I am quite new to database programming in VB.

I have a database that keeps track of different types of accommodations.....and also all the different units that eacy accommodation has.

For example........one accommodation is "Hotel"
and units could be 1, 2, 3, etc

What i want to do is make the program automatically display all the units in a data grid control for the selected type of accommodation.

I've tried doing this...

Adodc2.RecordSource = "SELECT tblUnits.UnitNumber FROM tblUnits WHERE tblUnits.AccTypeID = " & txtAccTypeID.Text

in order to change the data, but this doesnt do anything...the same units are displayd no matter which accommodation type is chosen.

What is the best way to do this?.......any help or advice you can give me would be welcome.

Thanx,
Clint

Data Control && DataGrid Ocx Problem
Hi,
I’m using a VB6 Pro SP4 (the latest). Here’s the problem:
1. I add Data control and DataGrid control (MSDATGRD.OCX) to the form
2. I connect Data control to Biblio.MDB and set RecordSource to one of the tables (Authors)
3. When I try to set DataSource of DataGrid I get the following error

Error Message:
No compatible data source was found for this control. Please add an OLEDB data source such as Data Enviroment or Microsoft ADO Data Control to the form or project.

I also get this message:

Error Message:
Can't bind this control to Data1. Select a different data source (eq - ADODC1, or DataEnviroment1...)

I would really appreciate your help for I need to use data control & bound it to data grid ocx.

Thanks.

Clear Data From Datagrid Control?
I am use to using the MSHFlexGrid and now since the datagrid is a .NET control and the FlexGrid is a COM control I am using the datagrid now. This is a really basic question. I have a datagrid I add data to from an Excel Doc using OleDb. Once it is filled with the file my program allows you to export the data. What I need is this how do I clear the datagrid when I import data from another Excel doc? There is no DataGrid1.Clear method.

Adding Data In DataGrid Control
Hello Friends,
i am using DataGrid Control and bounded it with database of SQL Server amd adding or updating data from Grid. But the problem is some times it will allow to add or update and sometimes it is not allowing me. I am also selected AddAllowNew property become true.
so what can i do plz reply me

SPPatel

Edited by - sppatel237 on 3/10/2004 7:52:00 PM

Changing Data In A Datagrid Control
I'm trying to modify data in a DataGrid control through code. I'm able to read what's currently there, but i can't seem to find the property/method that will let me change the data in a certain cell. I'm guessing it MUST be possible, since the user can do it during runtime. Any help would be greatly appreciated.

Thanks

Modifying Data In Datagrid Control ?
hi,
I set up my datagrid control based on a command object in a data environment.
Everything displays nicely, except that I can't edit, add or delete anything.
Does anybody know why ?

thanks,
Maggie

Problem In Populating Data From Datagrid To Excel Sheet
problem in populating data from datagrid to Excel sheet ...?

Problems Displaying Data From An Excel Sheet In A Datagrid
Does anybody know why when i pull data from an excel file using an ADO connection the only data that gets writen to the record set is the alphanumeric characters?
Let me explain i have a worksheet that gets imported into my application then is displayed to the user then the data gets writen to an Access database. In The excel sheet the colums that i am having problems with are formated in excel as text but when i create the recordset any cell that is not alphanummeric get a 'Null' value assigned to it. I have used differenet combinations of cursor types and LockTypes but nothing seeamed to work. In Access the 3 coulums in question are set up as 'text' data type.

The following is the code that creates the record set.

    Dim strSheetName As String
    Dim strOpen As String
    strSheetName = cmbSheetName.Text
    strOpen = "Select * from [" & strSheetName & "]"

    'get the all the data of the selected sheet
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open strOpen, cn, adOpenDynamic, adLockBatchOptimistic

    Set dataViewExcell.DataSource = rs
    frmGetExcell.dataViewExcell.Refresh
    cmdAdd.Enabled = True
    cmdCancel.Enabled = True

After the 'rs.Open' statment i loop through the record set and any cell value that was not an alphanumeric value was not put into the record set.

How To Display Selected Data From Excel To VB(Flexigrid Or Datagrid)
Hi

Can somebody please help me.

I would like to select a range of data from an Excel spreadsheet and display the selected data in either a flexigrid or datagrid in VB. The problem is I don't know how.

Thank you in advance for your responses.

Kind regards

Happy

Datagrid Control Does Not Refresh With Data Environment
Dear Friends,

I am facing a problem with DATAGRID IN VISUAL BASIC 6.00.

I am using a DATAENVIRONMENT with a Connection Object that has OneCommand Object.
The command Object uses the Following Parametric SQL Statement.

SELECT * FROM mondet WHERE cntno=?

Pls note that Mondet is a child table with multiple entries in CNTNOfield - (ACCESS Table)

The Datagrid has the following Data Properties (Thru the PropertiesPage)
The Name of The Data Environment is DE1
the Name of the Command Object is COMMAND1

DATAGRID1.DATASOURCE=DE1
DATAGRID1.DATAMEMBER=COMMAND1

The Datagrid refreshes the first time. But when we run the parametricSQL Statement again to create a new

Recordset, a new Recordset is Created (Checked the Recordcount Propertyof RECORDSET in MSGBOX)

but the DATAGRID is not updated. I have Tried "Rebind", "Refresh"Methods etc. but to no avail.

Infact even the "Clearfields" method does not Work after the FirstRecordset is Created.

Can Anyone of You Help Me out. Pls See that the Datagrid works fine(also Refreshes) with a ADODC Control. But I

Need DATAENVIRONMENT control to be used.

Thanx in Advance

Vinay Vekkot

Import/link XLS To Datagrid Using Data Control
How can I import/link excel file to datagrid using data control,
by coding.

Thank's
Jordan Sparta

Error 0 On DataGrid Linked With Ado Data Control
Does anyone know why I would get error 0 every time I touch the datagrid
during runtime(ie/update, etc)....Which library(s) do I have to reference to
use the datagrid and the ado data control?

Any help would be appreciated!! Thanks
Sonia

Automation Error (440) With ADO Data Control && DataGrid
                    Hello -
                    I am using VB6 against a SQL Server 7 db.

                    This is a very simple program whereby the datagrid is populated
                    by Adodc1  without any VB code.

                    I added a Timer control to refresh the data grid every 10
                    seconds   (Adodc1.Refresh)   This works very well.

                    Problem:  The program runs fine, refreshing the grid
                    periodically....  then after running for several minutes
                   (seems to  always be about 4 - 5  minutes)  get the
                   run-time    Automation   error (error 440)

                    The documentation for Error 440 says -" An error occurred
                    while  executing  a method"

                    Well,  the only method being executed is the .Refresh

                    I can only guess that there is a Timeout problem occurring
                    or  the   SQL Server db  is too busy to respond...?

                    I took the default values for Connection Timeout etc when I created
                    the controls.

                    Has anyone had experience with Automation errors?
                    Thanks for any help.
                    John

Insertion Of Data Using Datagrid Control. VB + SQL Server
Hi,
I have a problem in inserting the new record's data in DataGrid control.
I have got 2 tables. One table has a foreign key referring to the primary key of other table. Now records exist in primary key table and I am displaying the foreign key table in the Datagrid control. The fields displayed are partial for I dont want user to view all the fields of the table. Some to the fields get updated later as per the programming logic. But when I insert new record corresponding to the left out fields an error is displayed stating cannot insert null value.

Plz suggest a possible solution.

Unable To Link DataGrid To Data Control
I am using a Jet (Access) database with ADO. I am trying to display the data from my data control in a data grid. When I try adjusting the 'Data Source' property of the data grid, I get the following message "No compatible data source was found for this control. Please add an OLEDB data source such as the Data Environment or Microsoft ADO Data Control to the form or project." I get this message even though my Data Control is already on the same form.

Date Format Using ADO Data Control And DataGrid
When using the DataGrid with the ADO Data control in VB6 to view info from Access 97 databases, all "Date" fields are shown in US format. How do I display dates in UK "dd/mm/yy" format.
(The regional settings in control panel are set correctly for UK dates)
Note: Various Recordsets will have the "Date" field in different field positions.

Can U Please Help Me With The Complete Code For Copying Datagrid Data On To An Excel File
can u please help me with the complete code for copying datagrid data on to an excel file ...?

Updating MS Access Table With ADO Data Control From A Datagrid
Hi,

I'm populating a datagrid with the data of a MS access table using ADO Data Control. If I change the datagrid values, its automatically updating the table contents. Is there any property of the ADO data control, so that only on setting that value, the data grid changes should be reflected in the DB & not otherwise.

regards,
narayan

How To Read Specific Excel Range Data Into Datagrid Using Two Date Values?
Hi,

Below is the code that reads a row of Excel data from an Excel File into Visual Basic.NET program datagrid, the code only reads one row of data. I want to add more functionality to the program, on the first column of each rows in the Excel file is date, by adding two datetimepicker in the VB.NET program one listed as start date and other end date. I would like datagrid to return all the data between this two dates, I have trying various scenarios but all unsuccessful. Hope someone can help me.

  Dim oExcel As Object
  Dim i As Integer

        Dim ds As New DataSet
        ds = DataSet1
        ds.Clear()
        DataGrid1.DataSource = Nothing

        oExcel = CreateObject("Excel.Application")

        Dim dr As DataRow
        oExcel.Workbooks.Open(Application.StartupPath & "MyExcel.xls", , False)

        With oExcel.WorkBooks("MyExcel.xls").Worksheets(cmbBox2.SelectedItem)

            dr = ds.Tables("Table1").NewRow

            dr.Item("Date") = .Cells(1, 1).Value
            dr.Item("Min Price") = .Cells(1, 2).Value
            dr.Item("Max Price") = .Cells(1, 3).Value
            dr.Item("Closed Price") = .Cells(1, 4).Value
            dr.Item("Change") = .Cells(1, 5).Value
            dr.Item("Volume") = .Cells(1, 6).Value
            dr.Item("Foreign Inv.") = .Cells(1, 7).Value
            dr.Item("Securities Trust Co.") = .Cells(1, 8).Value
            dr.Item("Dealers") = .Cells(1, 9).Value

            ds.Tables("table1").Rows.Add(dr)

        End With

        Me.DataGrid1.DataSource = ds.Tables("table1")
        Me.DataGrid1.Refresh()

        oExcel.WorkBooks("MyExcel.xls").Close(True)
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

Edited by - roniwu on 11/13/2005 6:04:22 AM

Binding A DateTimePicker And/or ListBox To DataGrid To Control/restrict Input Data.
I have a DataGrid control with certain editable columns (some Numeric, some Date/Time fields) for which I would like to control/restrict input data. I want to acheive this by displaying a ListBox control or DateTimePicker control (as appropriate for that column) when a user clicks on the button in the cell.

I can display such controls external to the grid on the Button_Click event of the grid but I would rather have these pickers intrinsic to the grid...sure we've seen these all over the place?

Thanks in advance

Modifying Excel Data Using Data Control Object
This is a re-post of a previous message with a new wrinkle.

I am creating a form filled with text boxes. These text boxes will contain values that are also cell values of an Excel spreadsheet. I have included a Data Control object on the form that points to the Excel spreadsheet. Each of the text boxes on the form points to a particular field (cell) in the spreadsheet through it's DataSource / DataField properties. The Excel file contains only 2 rows of information. The 1st row is the data description, and the 2nd row contains the data. I have discovered that when I change the values in the text boxes, the corresponding cell values in the Excel spreadsheet do not change. If I then close the VB application, the Excel values are updated. My question is: how do I "force" the Excel worksheet to be updated without closing the VB application?

How Do I Use A Data Control To Update Excel
hai friends,

i had attached "ADO.ZIP"

will any one help to solve the problem.

i want the coding for "edit"

thanks in advance

EDIT By Shandy: Changed Subject from solve it to How Do I Use A Data Control To Update Excel

Edited by - Shandy on 3/22/2005 2:15:10 AM

Convert Excel Data To MSFlexgrid Control
hi,

I searched on the forum, regarding this but got confused.

Could anyone suggest me a good way to Bring my data from am Excel file into an MSFlexGrid control in VB application.

Your help is greatly appreciated!

Thanks!!!!
Jaga Malkareddy

Accessing Excel File Without Using Data Control Object In VB
I want to access Excel file without using Data Control Component or object, I am converting EXCEL file to SQL Server using VB6. Is it possible? I wish someone could help me
for that.

DARENZ

Sending Data Fom Excel To Another Program, How To Control Errors?
http://www.ilovemypc.com/excel%20sendkeys%20working%20code.zip

I'm looking for help on how to control errors in my program & couple of other things.

This is a vb6 program I use at work. I had a friend mine write it since i'm a novice. Basically this program reads specifically formatted excel spreadsheets and sends data from them to an active window. Before sending the data, this program cross references an access database. The program is not very neat, but it does the job.
You could actually modify this to do other stuff too...

At the url above (or attached), you can see the code i'm using, along with a sample spreadsheet and database.

Anyhow....

First this program checks for an active window "Inventory Adjustment". If it's not found, it displays an error. I would like it to exit if it finds the error, instead it displays a run time error 91.

Then the program launches the form, in which you select the spreadsheet you want to work with and you must TYPE IN the sheet name. If the sheet name does not exist, there is a runtime error 9 "Subscript out of range". Actually I would like it to diplay a msg box then exit or loop back to the beginning. Also a problem with this ... if the sheet name is not found, excel.exe is still open even though the program errored out.

Another issue I have with this program: sometimes, I get a runtime error 5 - if I restart the program it's fine...not sure what's wrong here...

Since i'm asking... another issue is this:
I'm basically sending data blindly to a specific active window. I'm sending the data then how ever many enter keys I need to goto the next field. In the {Item} variable, there is a max of 15 characters. If the max characters is reached, I don't want to send the enter key. Well in the vb program it's truncating everything to 15 characters. This is ok for the {Item} variable, but the {To Job} variable has 16 characters on occasion. The {To Job} field has a max of 22 characters (which no job is that long). As the program stands now, if the {To Job} variable has exactly 15 characters, the enter key is not sent after the variable, when it should be. So basically I need to change the program from truncating everything to 15 characters to just look at the {item} variable....(if it's 15 characters, don't send the enter key after that variable.

If anyone can help with this, I would appreciate it.

Also, if you're interested... I have a different version of the code that allows you to select the starting/ending row and columns.
http://www.ilovemypc.com/excel%20sendkeys%20working%20code2.zip

Why the program was created:

I work for an electrical contractor company. We use a database program called "ComputerEase". The spreadsheets contain material codes and qty info (basically what type of material will go into a specific floorplan of a house). I needed this program because otherwise I would have to print and manually type all day long. Now I just run this program..... I use the program on some sheets over and over, I just change one variable that I imput into the form.

Edited by - cyberfreak-vegas on 5/7/2003 11:51:40 AM

Refresh DataGrid(Microsoft DataGrid Control 6.0)
Hi,

I am having a problem refreshing the datagrid in the following scenario:

I have Form1 with ADODC control and DataGrid(Microsoft DataGrid Control 6.0). The grid (is used to display employee records) contains 5 columns (for example Office telephone number) . On clicking of a row on the grid the details window opens up. This window is used to update the employee information. On saving the employee record if Office telephone number is changed then the datagrid should be refreshed with latest data of the record. The refresh is done using the following command:
msgbox "activate"
adodcobject.refresh
datagridobject.refresh.

If the message box is commented then the refresh statements do not get executed. I need a solution for that.

Regards,
Rashmi.

How To Transfer Data In A Datagrid To Another Datagrid..?
Please help me in my project so that i can pass..

Im a freashman student in high school,
Im making a simple Computer Hardware Inventory system,

Datagrid1:
Item Description Serial Number Date Recorded
Mouse Optical SN-2007 03/27/2007

Datagrid2:
Item Description Serial Number Date Recorded

I want my data in datagrid1 will transfer to Datagrid2
please solve my problem..

youre help, is a big help to me!
Thank you in advance!! Godspeed!!

How To Copy Data From Datagrid Another Datagrid?
my problem is like that---

now i have 2 datagrid---datagrid1 & datagrid2
and 2 adodc-----adodc1 & adodc2

i have build some text that link with adodc1,i wanna build a command button that when i press it then the data from the text will copy to datagrid 2 and i can save it.

can any 1 show me the way?

thanks

How Do I Get This Information Into A Datagrid Control (datagrid)???
I have this information that i want to call out of a file and put into a data grid.
"AL","Alabama"
"AK","Alaska"
"AS","American Samoa"
"AZ","Arizona"
"AR","Arkanzas"
"CA","California"
"CZ","Canal Zone"
"CO","Colorado"
"CT","Connecticut"
"DE","Deleware"
"DC","District of Columbia"
"FL","Florida"
"GA","Georgia"
"GU","Guam"
"HI","Hawaii"
"ID","Idaho"
"IL","Illinois"
"IN","Indiana"
"IA","Iowa"
"KS","Kansas"
"KY","Kentucky"
"LA","Louisiana"
"ME","Maine"
"MD","Maryland"
"MA","Massachusetts"
"MI","Michigan"
"MN","Minnesota"
"MS","Mississippi"
"MO","Missouri"
"MT","Montana"
"NE","Nebraska"
"NV","Nevada"
"NH","New Hampshire"
"NJ","New Jersey"
"NM","New Mexico"
"NY","New York"
"NC","North Carolina"
"ND","North Dakota"
"OH","Ohio"
"OK","Oklahoma"
"OR","Oregon"
"PA","Pennsylvania"
"PR","Puerto Rico"
"RI","Rhode Island"
"SC","South Carolina"
"SD","South Dakota"
"TN","Tennessee"
"TX","Texas"
"TT","Trust Territories"
"UT","Utah"
"VT","Vermont"
"VA","Virginia"
"VI","Virgin Islands"
"WA","Washington"
"WV","West Virginia"
"WI","Wisconsin"
"WY","Wyoming"

And i want to get it into a data grid.
How do i do this??????