Subj:  How Do I?			Section: Database Issues
  To:  All				Monday, September 21, 1998 4:34:02 PM
From:  David Hendel, 74642,2103		#721112

I would like to move data from some Excel files into an Access Database.  I thought that Visual Basic 
may be the easiest way to go.  Using DDE, open the Excel files, move the data into a VB form, and then 
move the data into an Access Database.  Is this the best way to go?  The Excel files already exist, and I 
do need to move the data into a database.

I tried using DDE, but I go some sort of message saying that VB didn't support DDE unless there was 
some sort of extension.  I have VB 3.0 and 5.0 (I was using 5.0).  The version of Excel I was using came 
with Office 95, although the final program must be able to support the last 3 versions of Excel.

Anybody have any suggestions?

-----------------------------------------------------------------------------------

Subj:  How Do I?			Section: Database Issues
  To:  David Hendel, 74642,2103		Tuesday, October 06, 1998 12:46:09 AM
From:  Calvin G. Smith, 102226,2127	#721733

Hi David!

>>>> I would like to move data from some Excel files into an Access Database.  I thought that Visual 
Basic may be the easiest way to go.  Using DDE, open the Excel files, move the data into a VB form, 
and then move the data into an Access Database.  Is this the best way to go?  <<<<

Well, I wouldn't recommend that approach. That's too much work! :-)

>>>> The Excel files already exist, and I do need to move the data into a database... Anybody have any 
suggestions? <<<<

Yes, assuming that the target table schema(s) already exist in your MDB file, here's an elegant 
[sample] approach to consider:

     strSQL$ = "Insert Into tblCodeDiskSampleFromExcel " & _
                    "IN 'C:\Calvin\CodeDiskFiles\CodeDisk.MDB' " & _
                         "Select * From [Systems$] IN 'C:\Calvin\CodeDiskFiles\Systems.xls' 'EXCEL 5.0;'"

     dbMyDB.Execute strSQL$


Calvin Smith - Sr. Visual Basic/MS Access Consultant
********************************************************
http://www.CalvinSmithSoftware.com - Automation Code
********************************************************

-----------------------------------------------------------------------------------

Subj:  How Do I?			Section: Database Issues
  To:  Calvin G. Smith, 102226,2127	Wednesday, October 07, 1998 5:11:10 PM
From:  David Hendel, 74642,2103		#721811

Hi Calvin;
thanks for the reply.  I'll give it a try, but the data in the spreadsheet that I want to put into the database is 
not all in tabular form, and not all the columns have headers.  I really need to be able to pick and 
choose the data from the spreadsheet to populate the database.

-----------------------------------------------------------------------------------

Subj:  How Do I?			Section: Database Issues
  To:  David Hendel, 74642,2103		Friday, October 09, 1998 12:07:03 AM
From:  Calvin G. Smith, 102226,2127	#721881

Hi David!

>>> thanks for the reply. <<<

You're welcome! :-)

>>>  I'll give it a try, but the data in the spreadsheet that I want to put into the database is not all in 
tabular form, and not all the columns have headers.  I really need to be able to pick and choose the 
data from the spreadsheet to populate the database. <<<

Hmmm, not pretty at all! The best that I can suggest from here, for your scenario, is to create an OLE 
object to your Excel file, and have your code loop its way through the individual cells (columns and 
rows) finding data, and comparing it to whatever strings that you're looking for. Then if found, write the 
value(s) to an open Access recordset.

So for example, if I'm looking for the string "Programmer/Analyst", which my code finds in A1, and I 
know that the actual values start right beneath that at say A2, then inside of some *incrementing loop*, 
my code should look something like the following [without the hard-coding]:

If xlObj.Cells(iRow, iColumn + 1).value = "Programmer/Analyst" Then
     With rsMyAccessRecordSet
          .AddNew
          !MyRecordSetColumn = xlObj.Cells(iRow + 1, iColumn + 1).value
          .Update
     End With
End If

Calvin Smith - Sr. Visual Basic/MS Access Consultant
********************************************************
http://www.CalvinSmithSoftware.com - Automation Code
********************************************************