Solved

Automated Excel -> SQL Server Data Transfer

Posted on 1998-06-23
4
155 Views
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)?
0
Comment
Question by:mlee
4 Comments
 
LVL 4

Expert Comment

by:tomook
ID: 1464123
What information are you trying to pull of the chart?
0
 
LVL 2

Expert Comment

by:Llandr
ID: 1464124
Are all the charts alike?
0
 

Author Comment

by:mlee
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.

Thanks
0
 
LVL 1

Accepted Solution

by:
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
   
 Do
  DoEvents
 
  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
   Loop
     

  Else

     chkStart.value = True
       
  End If

End Sub

Private Sub cmdEnd_Click()
  Unload Me
End
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")
Xobj.Activate

End Sub

'Above program is Example.
0

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