Solved

excel2k

Posted on 2001-08-14
7
142 Views
Last Modified: 2010-05-02
without having to buy the book  is there a good tutorial on line for talking to excel through vis basic i.e setting up fields  rows etc..?
or has anyone any code that would start me off example how to name 3 columns and insert data in thieir respective rows?
0
Comment
Question by:TheWalrus
7 Comments
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Here's a good place to start

Automating Microsoft Excel
http://www.djpate.freeserve.co.uk/AutoExcl.htm
0
 

Author Comment

by:TheWalrus
Comment Utility
David Greene
excuse my ignorance but isnt that site exclusively for delphi programming?
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
Comment Utility
Dim appExcel As Excel.Application
On Error Resume Next
Set appExcel = GetObject(,"Excel.Application")
If appExcel Is Nothing Then
  Set appExcel = CreateObject("Excel.Application")
End If
On Error Goto 0
'This has obtained a reference to either an existing excel session or if one does not exist to a new excel session

appExcel.Workbooks.Add 'Add a new workbook
With appExcel.ActiveWorkbook
  .ActiveSheet.Cells(1,1).Value = "Column1"
  .ActiveSheet.Cells(1,2).Value = "Column2"
End With

appExcel.ActiveWorkbook.SaveAs "MyExcelSheet.xls",True
appExcel.Quit 'If you want to quit that is
Set appExcel = Nothing 'Destroy the reference, if you haven't quit then this will NOT close excel.

Essentially the first part is the most important, as this is what creates the automation link between VB and excel. The properties and methods are available for you to see in the object browser and are effectively the same as the statements that are generated if you create/record a macro in excel itself.
 
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:Paullkha
Comment Utility
Public Sub JetExcel()
Dim oConn As adodb.Connection
Dim oCmd As adodb.Command
Dim ors As adodb.Recordset

' Open a connection to the Excel spreadsheet
Set oConn = New adodb.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=Expenses.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=Yes;"";"

' Create a command object and set its ActiveConnection
Set oCmd = New adodb.Command
oCmd.ActiveConnection = oConn

' This SQL statement selects a cell range in the "Expenses"  worksheet.
'    oCmd.CommandText = "SELECT * from `Expenses$A2:C4`"

' This SQL statement selects a named cell range defined in the workbook.
oCmd.CommandText = "SELECT * from `Range1`"

' Open a recordset containing the worksheet data.
Set ors = New adodb.Recordset
ors.Open oCmd, , adOpenKeyset, adLockOptimistic

Debug.Print ors.RecordCount

' Update last row
ors.MoveLast
ors(0).Value = -1
ors.Update

' Add a new row
ors.AddNew
ors(0).Value = 7
ors(1).Value = 8
ors(2).Value = 9
ors.Update
Debug.Print ors.RecordCount



End Sub

Public Sub ODBCExcel()


Dim oConn As adodb.Connection
Dim ors As adodb.Recordset


' Create and open a new ADO Connection
Set oConn = New adodb.Connection
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                     "FIL=excel 8.0;" & _
                     "DefaultDir=C:\My Documents\test;" & _
                     "MaxBufferSize=2048;" & _
                     "PageTimeout=5;" & _
                     "DBQ=C:\My documents\test\testit.xls;"

' Create the Recordset
Set ors = New adodb.Recordset
' Open the Recordset using a Named Ranged
'oRS.Open "Select * from myRange1", oConn, adOpenStatic, adLockBatchOptimistic, adCmdText

' Open the Recordset using the Sheet Name
  ors.Open "[Sheet1$]", oConn, adOpenStatic,
End Sub
0
 
LVL 2

Expert Comment

by:Paullkha
Comment Utility
Just be careful with Named Ranges vs [Sheet1$] - note dollar sign. Actual sheet name would be Sheet1.

Linked Excel Sheet = structure can not be modified (adding fields is definetly not allowed.)
0
 

Author Comment

by:TheWalrus
Comment Utility
paullkha
this last line  comes up red in my ide
ors.Open "[Sheet1$]", oConn, adOpenStatic,
0
 

Author Comment

by:TheWalrus
Comment Utility
TimCottee: does what i asked for Thanks.
paullkha:  thanks to you too.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now