Solved

Automated Excel -> SQL Server Data Transfer

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

828 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