Pasting a column of numbers in Excel into a grid/db grid in VB 
Author Message
 Pasting a column of numbers in Excel into a grid/db grid in VB

I want to be able to enter records into a VB grid of some form by copying  a
column of numbers from Excel and pasting it (as input) into a DBGrid or
something similiar. Then records can then be committed to the database.

Can this be done? and if so How????

Thanks,

Liam.



Tue, 15 May 2001 03:00:00 GMT  
 Pasting a column of numbers in Excel into a grid/db grid in VB
Quote:

> I want to be able to enter records into a VB grid of some form by copying  a
> column of numbers from Excel and pasting it (as input) into a DBGrid or
> something similiar. Then records can then be committed to the database.

> Can this be done? and if so How????

Hi Liam.

There are several ways of doing this.

If all you want to do is to save Excel data in a database format, I'd
imagine that you can make an Excel macro do this for you, and you don't
even have to go through the DBGrid or anything like it.
Even if you want to do something with your data (formatting etc.), but
without the user seeing or doing anything, again you do not need the
DBGrid functionality. Again you could do it all in an Excel Macro.
In these cases, look at 4 and 5 below:

If you do need the DBGrid function, you need to do most of the following
steps:

1. Link to Excel from VB
2. Transfer the data
3. Present them to the user, format etc.
4. Link to database
5. Transfer data

1. Link to Excel from VB
The easiest way of doing this is by the use of OLE automation. You
create an Excel object in VB and can then use all the Excel object
properties and methods to retrieve the data. Excel objects, properties
and methods are in the object browser (F2) and in the Excel Basic
Helpfile.
Example:
Dim XLApp as Excel.Application
Dim XLSheet as Excle.Worksheet
Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open "Yourfile.xls"
Set XLSheet = XLApp.ActiveWorkbook.Activesheet

2. Transfer the data
Assign the data to whichever variables or controls you want.
Example:
(following on from above:)
txtMyText.Text=XLSheet.Range("B2").Value

3. Present them to the user, format etc.
'Normal' VB code according to what you want to do

4. Link to database (Note that this can be done straight in Excel VBA,
you don't need VB)
Dim db as Database
Dim MyTable as Recordset

Set DB = OpenDatabase("Myfile.mdb")
Set MyTable = DB.OpenRecordset("MyTable")

5. Transfer data to database (Note that this can be done straight in
Excel VBA, you don't need VB)
Mytable.Edit or .AddNew  
Mytable("Fieldname") = txtMyText.Text
etyc.
MyTable.Update

Notes:
- Binding your database data with the DBGrid may be very easy now, but
very quickly makes it more difficult later on. The most flexible
approach is to deal with the updating of your database and the transfer
of data either way yourself, as shown above, without using bound data
controls. That way, you can use MSFlexGrid, Listboxes, Comboboxes,...
whatever, and perform all the operations you want. The only but is that
you need to make sure that the records are consistent, check for current
records etc.
- To use the Excel objects in VB, you need to have referenced the Excel
Object library. Do this with Project|References.
- To use the DAO database objects, you must have referenced the DAO
library
- It is a lot easier if you have Access installed to make the initial
.mdb file with Access than to do it through hard coding. Otherwise, you
need to use the Tabledefs property of the database object.
- Set your objects to Nothing when you don't need them, for the sake of
system resources. I.e. after you've finished transferring data from
Excel, Set XLApp=Nothing.

HTH,
Morten.
--

.sig
.disclaimer



Tue, 15 May 2001 03:00:00 GMT  
 Pasting a column of numbers in Excel into a grid/db grid in VB
Hi Pahle,

Thank you for the help. Althought this definitely will solve the problem, I
was hoping to implement a much simpler workaround.

At present the users are used to working with multiple spreadsheets.

I want to keep this familiarity. Basically Allowing them to Alt & Tab to
Excel. Select the columns they wish to copy. Copy them to the clipboard. Alt
& Tab back to the VB program. Then paste the column ideally into an
MSFlexgrid.

Can this be done....?

Thanks in advance, (please reply via email)

Liam



Sat, 19 May 2001 03:00:00 GMT  
 Pasting a column of numbers in Excel into a grid/db grid in VB
I didn't see the beginning of this thread, so I may be way off topic
here but the answer to the question that I think you asked (phew!) is
Yes, it can be done.

If you use VisualTools Developers Suite it has an Excel control which
you can programatically paste to and from the clipboard object.
Otherwise, this will paste the data into the current control.

Private Sub mnuPaste_Click ()  
If TypeOf Screen.ActiveControl Is TextBox Then
      Screen.ActiveControl.SelText = Clipboard.GetText()
   ElseIf TypeOf Screen.ActiveControl Is ComboBox Then
      Screen.ActiveControl.Text = Clipboard.GetText()
   ElseIf TypeOf Screen.ActiveControl Is PictureBox _        
     Then  Screen.ActiveControl.Picture =  Clipboard.GetData()
   ElseIf TypeOf Screen.ActiveControl Is ListBox Then
      Screen.ActiveControl.AddItem Clipboard.GetText()  
   Else
      ' No action makes sense for the other controls.  
  End If
End Sub

Obviously, you will need to implement more code to drop it into a
MsFlexGrid using (if I remember correctly) the TextMatrix property.

As I recall, Excel drops the text onto the clipboard in many different
formats - the text version has columns separated with a Chr(9) and
rows with Chr(13).

HTH
Sara

On Tue, 1 Dec 1998 11:52:15 -0000, "McAllister"

Quote:

>Hi Pahle,

>Thank you for the help. Althought this definitely will solve the problem, I
>was hoping to implement a much simpler workaround.

>At present the users are used to working with multiple spreadsheets.

>I want to keep this familiarity. Basically Allowing them to Alt & Tab to
>Excel. Select the columns they wish to copy. Copy them to the clipboard. Alt
>& Tab back to the VB program. Then paste the column ideally into an
>MSFlexgrid.

>Can this be done....?

>Thanks in advance, (please reply via email)

>Liam




Sat, 19 May 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Pasting a column of numbers in Excel into a grid/db grid in VB

2. Pasting a column of numbers in Excel into a grid/db grid in VB

3. copy & paste from excel to VB grid

4. DB Grid to DB Grid Question!

5. Q: Copy in Grid, Paste in Excel

6. True DB Grid 5.0 - Sort a grid

7. Grid columns numbered right to left

8. Grid Column Number

9. Adding Columns to DB grid

10. MS Data grid OLE DB - Column Heading Alignment problem

11. MS Data grid OLE DB - Column Alignment problem

12. Moving columns in DB Grid

 

 
Powered by phpBB® Forum Software