[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel2k

Posted on 2001-08-14
7
Medium Priority
?
161 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
ID: 6384545
Here's a good place to start

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

Author Comment

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

Accepted Solution

by:
TimCottee earned 400 total points
ID: 6384620
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Expert Comment

by:Paullkha
ID: 6384758
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
ID: 6384771
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
ID: 6384809
paullkha
this last line  comes up red in my ide
ors.Open "[Sheet1$]", oConn, adOpenStatic,
0
 

Author Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

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…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

873 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