Solved

excel2k

Posted on 2001-08-14
7
155 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
[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
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 100 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

733 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