Automated Excel -> SQL Server Data Transfer

Posted on 1998-06-23
Last Modified: 2011-09-20
I'm trying to write an application that will open a connection to several excel charts, open a recordset, and insert each record set into a SQL Server table (one per chart).  Any ideas (and possibly code)?
Question by:mlee

Expert Comment

ID: 1464123
What information are you trying to pull of the chart?

Expert Comment

ID: 1464124
Are all the charts alike?

Author Comment

ID: 1464125
All the tables look essentially the same and the rows of the tables are identical (i.e. row 1 are the database fields). I guess what I'm asking is how do I connect via RDO, DAO, how do I access the rows/fields, and what the difference is between RDO and DAO.


Accepted Solution

chdy earned 200 total points
ID: 1464126
Option Explicit
Dim iSw%

Dim Xobj As Object
Dim XobjApp As Object

Dim A1%, B1%, C1%, D1%, E1%, F1%, G1%, H1%, I1%, J1%
Dim K1%, L1%, M1%, N1%, O1%, P1%

Dim aCount%: Dim bCount%
Dim cCount%: Dim dCount%
Dim eCount%: Dim fCount%
Dim gCount%: Dim hCount%
Dim iCount%: Dim jCount%
Dim kCount%: Dim lCount%
Dim mCount%: Dim nCount%
Dim oCount%: Dim pCount%

Private Sub chkStart_Click(value As Integer)

aCount = 0: bCount = 0
cCount = 0: dCount = 0
eCount = 0: fCount = 0
gCount = 0: hCount = 0
iCount = 0: jCount = 0
kCount = 0: lCount = 0
mCount = 0: nCount = 0
oCount = 0: pCount = 0

Xobj.Parent.Windows(1).Visible = True
Set XobjApp = Xobj.Application
XobjApp.Visible = True

If value = False Then
  If chkStart.value = True Then
     Exit Do
  End If
  iSw = Int((16 * Rnd) + 1)      
  Debug.Print iSw

  Select Case iSw
         Case 16
               pCount = pCount + 1
              Xobj.Range("P1").value = pCount
         Case 1
              aCount = aCount + 1
              Xobj.Range("A4").value = aCount
         Case 2
              bCount = bCount + 1
              Xobj.Range("B4").value = bCount
         Case 3
              cCount = cCount + 1
              Xobj.Range("C4").value = cCount
         Case 4
              dCount = dCount + 1
              Xobj.Range("D4").value = dCount
         Case 5
              eCount = eCount + 1
              Xobj.Range("E4").value = eCount
         Case 6
              fCount = fCount + 1
              Xobj.Range("F4").value = fCount
         Case 7
              gCount = gCount + 1
              Xobj.Range("G4").value = gCount
         Case 8
              hCount = hCount + 1
              Xobj.Range("H4").value = hCount
         Case 9
              iCount = iCount + 1
              Xobj.Range("I4").value = iCount
         Case 10
              jCount = jCount + 1
              Xobj.Range("J4").value = jCount
         Case 11
              kCount = kCount + 1
              Xobj.Range("K4").value = kCount
         Case 12
              lCount = lCount + 1
              Xobj.Range("L4").value = lCount
         Case 13
              mCount = mCount + 1
              Xobj.Range("M4").value = mCount
         Case 14
              nCount = nCount + 1
              Xobj.Range("N4").value = nCount
              Xobj.Range("N1").value = txtYY + "Year" & txtMM + "Month" _
 & txtDD + "Day"
         Case 15
              oCount = oCount + 1
              Xobj.Range("O4").value = oCount
      End Select


     chkStart.value = True
  End If

End Sub

Private Sub cmdEnd_Click()
  Unload Me
End Sub

Private Sub cmdStart_Click()
 Dim value As Boolean
   value = False
   chkStart_Click (value)
End Sub

Private Sub Form_Load()

Set Xobj = GetObject("c:\my documents\Data1.xls")

End Sub

'Above program is Example.

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question