×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Visual Basic (Classic) FAQ

Integration With MS Excel

General guide to using Excel in VB by ADoozer
Posted: 27 Mar 03 (Edited 16 Dec 03)

Please note this is a work in progress (still).

Note also that a reference to the "microsoft excel n.0 object library" is required (project->references),

A general word of warning from personal experience!!! when using IgnoreRemoteRequests always reset this value to false b4 exiting your app!!!!!!

'code by johnyingling
#Const blnEarly = True  ' Use Intellisense
#If blnEarly Then
    Dim myExcelApplication As Excel.Application
    Dim myExcelWorkbook As Excel.Workbook
    Dim myExcelWorksheet As Excel.Worksheet   
    Dim myExcelChart As Excel.Chart
#Else
    Dim myExcelApplication As Object
    Dim myExcelWorkbook As Object
    Dim myExcelWorksheet As Object
    Dim myExcelChart As Object
#End If

Dim myCommonDialog As Object

Private Sub CreateExcelApplication(Invisible As Boolean, NumWrkSht As Integer, _
                                    NameSheets As Boolean)
    
    'creates an excel object with specified number of sheets
    If blnEarly Then
        Set myExcelApplication = New Excel.Application
    Else
        Set myExcelApplication = CreateObject("Excel.Application")
    End If
    
    myExcelApplication.SheetsInNewWorkbook = NumWrkSht
    
    Set myExcelWorkbook = myExcelApplication.Workbooks.Add
    
    If NameSheets = True Then
        Dim i As Integer
        Dim myString As String
        i = 1
        For Each Sheet In myExcelWorkbook.Sheets
            Set myExcelWorksheet = Sheet
            myString = InputBox("Enter Name For Worksheet " & i, "Name Worksheet", _
                                "Sheet" & i)
            If myString = "" Then myString = "Sheet" & i
            myExcelWorksheet.Name = myString
            i = i + 1
        Next Sheet
    End If
    
    Set myExcelWorksheet = myExcelWorkbook.ActiveSheet
  
    If Invisible = False Then myExcelApplication.Visible = True
    
End Sub

'the excel file constants:-
'xlAddIn , xlCSV , xlCSVMac, xlCSVMSDOS, xlCSVWindows, xlCurrentPlatformText, xlDBF2
'xlDBF3 , xlDBF4 , xlDIF, xlExcel2, xlExcel2FarEast, xlExcel3, xlExcel4, xlExcel4Workbook
'xlExcel5 , xlExcel7, xlExcel9795, xlHtml, xlIntlAddIn, xlIntlMacro, xlSYLK, xlTemplate
'xlTextMac , xlTextMSDOS, xlTextPrinter, xlTextWindows, xlUnicodeText, xlWJ2WD1, xlWK1
'xlWK1ALL , xlWK1FMT, xlWK3, xlWK4, xlWK3FM3, xlWKS, xlWorkbookNormal, xlWorks2FarEast
'xlWQ1 , xlWJ3, xlWJ3FJ3


Private Sub CloseExcel(Sav As Boolean, Optional myFileFormat As Integer)
    
    If Sav = True Then
        'save excel, prompt for save name
        'TODO: write code to pop up save as dialog box

        myfile = "C:\temp"
        If myFileFormat = 0 Then
            myExcelWorkbook.SaveAs myfile, , , , , False
        Else
            myExcelWorkbook.SaveAs myfile, myFileFormat, , , , False
        End If
    Else
        'dont save
        myExcelWorkbook.Close False
    End If
    
    myExcelApplication.Quit
    
End Sub

Private Sub CopyWorksheet(WrkShtName As String, Aftr As Boolean, _
                        Optional CopySht As String, Optional AftrSht As String)
    
    If Not CopySht = "" Then
        Set myExcelWorksheet = myExcelWorkbook.Worksheets(CopySht)
    Else
        Set myExcelWorksheet = myExcelWorkbook.ActiveSheet
    End If
    
    If Not AftrSht = "" Then
        Dim xlCopyPlace As Excel.Worksheet
        Set xlCopyPlace = myExcelWorkbook.Worksheets(AftrSht)
    Else
        Set xlCopyPlace = myExcelWorksheet
    End If
    
    'copy worksheet
    If Aftr = True Then
        myExcelWorksheet.Copy , xlCopyPlace
    Else
        myExcelWorksheet.Copy xlCopyPlace
    End If
    
    'rename sheet as appropriate
    Set myExcelWorksheet = myExcelWorkbook.ActiveSheet
    myExcelWorksheet.Name = WrkShtName
    
    'clean up
    Set xlCopyPlace = Nothing
    
End Sub

This function is still very basic and open to other errors, i will be adding more at a later date

Private Function IsExcelAppOpen() As Boolean
    
    On Error Resume Next
    
    'Try first to use an existing instance.
    Set myExcelApplication = GetObject(, "Excel.Application")
    
    If Err = 429 Then
        'Excel isn't running, so start it.
        IsExcelAppOpen = False
        Err.Clear
    Else
        'Excel is running
        Set myExcelApplication = Nothing
        IsExcelAppOpen = True
    End If
    
End Function

heres the beginnings (well a few lines anyway, im a little busy right now) of writing cells

Private Sub AddToWorksheet()
    
    myExcelWorksheet.Range("C7:G11").Font.Bold = True
    
    myExcelWorksheet.Range("B2:H16").Value = "Hello World"
    
    myExcelWorksheet.Range("D8:F9").WrapText = True
    
    myExcelWorksheet.Range("E5").VerticalAlignment = xlVAlignCenter
    
    myExcelWorksheet.Range("E5").HorizontalAlignment = xlHAlignCenter
    
    myExcelWorksheet.Range("E5").Font.Color = vbRed

    myExcelWorksheet.Range("H15").Font.Size = 32

    myExcelWorksheet.Cells(9, 5) = "Im in the middle"

    myExcelWorksheet.Range("D3").BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic

    myExcelWorksheet.Range("B2:E6").Borders(xlDiagonalUp).LineStyle = xlContinuous
    
    myExcelWorksheet.Cells(1, 1).Borders(xlDiagonalUp).LineStyle = xlContinuous

    myExcelWorksheet.Range("E9").Interior.Color = vbBlue
    
    myExcelWorksheet.Columns("B:H").AutoFit    

    myExcelWorksheet.Columns("D:F").ColumnWidth = 30

    myExcelWorksheet.Range("C2:H2").Insert (xlShiftDown)

    Columns("D").Select
    Selection.Cut
    Columns("C").Select
    ActiveSheet.Paste

End Sub

The first link to a thread:-

this is by JGoodman00 refering to drawing embeded graphs based on ADO references (i think)... its something i dont know anything about so im gonna take there word on this...

Thread222-512801

useful info from other members:-

posted my MikeWar (TechnicalUser) Jul 18, 2003
This method stops users from loading their workbooks into your Excel by forcing them to use a new instance of Excel.

Dim oExcel as object
......
Set oExcel = CreateObject("Excel.Application")
oExcel.IgnoreRemoteRequests = True
....

if anyone spots a mistake or has a suggestion to enhance the faq (criticisms welcome) feel free to let me know.

thank you to all contributers!

Back to Visual Basic (Classic) FAQ Index
Back to Visual Basic (Classic) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close