INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

Visual Basic (Microsoft) Versions 5/6 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 (Microsoft) Versions 5/6 FAQ Index
Back to Visual Basic (Microsoft) Versions 5/6 Forum

My Archive

Resources

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