• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • Last Modified:

excel2k

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
TheWalrus
Asked:
TheWalrus
1 Solution
 
Dave_GreeneCommented:
Here's a good place to start

Automating Microsoft Excel
http://www.djpate.freeserve.co.uk/AutoExcl.htm
0
 
TheWalrusAuthor Commented:
David Greene
excuse my ignorance but isnt that site exclusively for delphi programming?
0
 
TimCotteeHead of Software ServicesCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
PaullkhaCommented:
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
 
PaullkhaCommented:
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
 
TheWalrusAuthor Commented:
paullkha
this last line  comes up red in my ide
ors.Open "[Sheet1$]", oConn, adOpenStatic,
0
 
TheWalrusAuthor Commented:
TimCottee: does what i asked for Thanks.
paullkha:  thanks to you too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now