No one seems to know this answer
[218 byte] By [brix_zx2] at [2007-11-19 7:27:34] 
I went searching through the forums only to find my question but no answers. Is there a way to paste Excel data to a data grid? And if that's impossible how can I import a large amount of records to my database?
# 1 Re: No one seems to know this answer
>Is there a way to paste Excel data to a data grid? Don't know, but if so, it's unlikelyf a trivial task.
> And if that's impossible how can I import a large amount of records...
Where/in what form are the "records" now?
> ...to my database?
What kind of database?
DSJ at 2007-11-10 3:17:49 >
# 2 Re: No one seems to know this answer
Sorry been frustrated all day trying to figure this out, my explanation got sparse.Our base gets these checklists that are in Excel format. I need to be able to get
the data from the excel sheet into my Access 2000 DB programatically. I was
trying: (Psuedo Code)
Fill dataset with Access DB
Fill Dataset2 with excel data
AccessDataset.Merge(Excel Dataset)
but that doesn't work.
brix_zx2 at 2007-11-10 3:18:57 >
# 3 Re: No one seems to know this answer
Transferring data from Excel to Datagrid is really quite straightforward. Follow these steps:1. Create a text file with .xml as the filetype attached to filenumber 'fmxl'.
2. For each row of Excel data read the column value to variables.
For example firstname and lastname
3. Print to the text file, the following two lines: for example for fileunit 'fmxl' (integer):
Print(fxml, "<?xml version='1.0'?>" & vbCrLf)
Print(fxml, "<NewData>" & vbCrLf)
Then start typing each line of data
Print(fxml, "<Members>" & vbCrLf)
Print(fxml, "<FirstName>" & fname & "</FirstName>" & vbCrLf)
Print(fxml, "<LastName>" & lname & "</LastName>" & vbCrLf)
Print(fxml, "</Members>" & vbCrLf)
4. After all rows from Excel have been written, add the last line to signify end of data:
Print(fxml, "</NewData>" & vbCrLf)
5. Close the file.
You now have an xml file, which you can directly import to a datagrid.
Good luck.
Gizmo001 at 2007-11-10 3:19:53 >
# 4 Re: No one seems to know this answer
I just recently (a couple days ago) had to do something like this myself. What I think you are looking for is Access's TransferSpreadsheet function. From MSDN:
expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
Here's the example given for a lotus spreadsheet:
DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"
Hope this helps!
Note: This is from Access 2003's language ref. I did not get a chance to look it up for Access 2000 but I believe it exists.
TSmooth at 2007-11-10 3:20:59 >
# 5 Re: No one seems to know this answer
I believe you can access excel files with ADO.NET.The easiest way to put the data in a database would be to open the excel file in excel and save it as a CSV file, then it should be pretty simple to import into a database table (depends on what kind of database you're using).
cmiskow at 2007-11-10 3:21:55 >
