Solved

Automated Excel -> SQL Server Data Transfer

Posted on 1998-06-23
4
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 21 hours left to enroll

627 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