Solved

Automated Excel -> SQL Server Data Transfer

Posted on 1998-06-23
4
157 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBS file using code from 2nd file (txt or vbs) 4 55
passing parameter in sql procedure 9 70
VBA filters 2 81
Set email body to html using vbscript 6 90
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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…

734 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