General guide to using Excel in VB

[color green]Please note this is a work in progress (still).[/color]

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!!!!!!

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

[color blue]Dim[/color] myCommonDialog [color blue]As Object[/color]

[color blue]Private Sub[/color] CreateExcelApplication(Invisible [color blue]As Boolean[/color], NumWrkSht [color blue]As Integer[/color], _
NameSheets [color blue]As Boolean[/color])

[color green]'creates an excel object with specified number of sheets[/color]
[color blue]If[/color] blnEarly [color blue]Then[/color]
[color blue]Set[/color] myExcelApplication = [color blue]New[/color] Excel.Application
[color blue]Else[/color]
[color blue]Set[/color] myExcelApplication = CreateObject("Excel.Application")
[color blue]End If[/color]

myExcelApplication.SheetsInNewWorkbook = NumWrkSht

[color blue]Set[/color] myExcelWorkbook = myExcelApplication.Workbooks.Add

[color blue]If[/color] NameSheets = [color blue]True Then[/color]
[color blue]Dim[/color] i [color blue]As Integer[/color]
[color blue]Dim[/color] myString [color blue]As String[/color]
i = 1
[color blue]For Each[/color] Sheet [color blue]In[/color] myExcelWorkbook.Sheets
[color blue]Set[/color] myExcelWorksheet = Sheet
myString = InputBox("Enter Name For Worksheet " & i, "Name Worksheet", _
"Sheet" & i)
[color blue]If[/color] myString = "" [color blue]Then[/color] myString = "Sheet" & i
myExcelWorksheet.Name = myString
i = i + 1
[color blue]Next[/color] Sheet
[color blue]End If[/color]

[color blue]Set[/color] myExcelWorksheet = myExcelWorkbook.ActiveSheet

[color blue]If[/color] Invisible = [color blue]False Then[/color] myExcelApplication.Visible = [color blue]True[/color]

[color blue]End Sub[/color]

[color green]'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[/color]

[color blue]Private Sub[/color] CloseExcel(Sav [color blue]As Boolean[/color], [color blue]Optional[/color] myFileFormat [color blue]As Integer[/color])

[color blue]If[/color] Sav = [color blue]True Then[/color]
[color green]'save excel, prompt for save name
'TODO: write code to pop up save as dialog box[/color]
myfile = "C:\temp"
[color blue]If[/color] myFileFormat = 0 [color blue]Then[/color]
myExcelWorkbook.SaveAs myfile, , , , , [color blue]False[/color]
[color blue]Else[/color]
myExcelWorkbook.SaveAs myfile, myFileFormat, , , , [color blue]False[/color]
[color blue]End If[/color]
[color blue]Else[/color]
[color green]'dont save[/color]
myExcelWorkbook.Close [color blue]False[/color]
[color blue]End If[/color]

myExcelApplication.Quit

[color blue]End Sub[/color]

[color blue]Private Sub[/color] CopyWorksheet(WrkShtName [color blue]As String[/color], Aftr [color blue]As Boolean[/color], _
[color blue]Optional[/color] CopySht [color blue]As String[/color], [color blue]Optional[/color] AftrSht [color blue]As String[/color])

[color blue]If Not[/color] CopySht = "" [color blue]Then[/color]
[color blue]Set[/color] myExcelWorksheet = myExcelWorkbook.Worksheets(CopySht)
[color blue]Else[/color]
[color blue]Set[/color] myExcelWorksheet = myExcelWorkbook.ActiveSheet
[color blue]End If[/color]

[color blue]If Not[/color] AftrSht = "" [color blue]Then[/color]
[color blue]Dim[/color] xlCopyPlace [color blue]As[/color] Excel.Worksheet
[color blue]Set[/color] xlCopyPlace = myExcelWorkbook.Worksheets(AftrSht)
[color blue]Else[/color]
[color blue]Set[/color] xlCopyPlace = myExcelWorksheet
[color blue]End If[/color]

[color green]'copy worksheet[/color]
[color blue]If[/color] Aftr = [color blue]True Then[/color]
myExcelWorksheet.Copy , xlCopyPlace
[color blue]Else[/color]
myExcelWorksheet.Copy xlCopyPlace
[color blue]End If[/color]

[color green]'rename sheet as appropriate[/color]
[color blue]Set[/color] myExcelWorksheet = myExcelWorkbook.ActiveSheet
myExcelWorksheet.Name = WrkShtName

[color green]'clean up[/color]
[color blue]Set[/color] xlCopyPlace = [color blue]Nothing[/color]

[color blue]End Sub[/color]

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

[color blue]Private Function[/color] IsExcelAppOpen() [color blue]As Boolean[/color]

[color blue]On Error Resume Next[/color]

[color green]'Try first to use an existing instance.[/color]
[color blue]Set[/color] myExcelApplication = GetObject(, "Excel.Application")

[color blue]If[/color] Err = 429 [color blue]Then[/color]
[color green]'Excel isn't running, so start it.[/color]
IsExcelAppOpen = [color blue]False[/color]
Err.Clear
[color blue]Else[/color]
[color green]'Excel is running[/color]
[color blue]Set[/color] myExcelApplication = [color blue]Nothing[/color]
IsExcelAppOpen = [color blue]True[/color]
[color blue]End If[/color]

[color blue]End Function[/color]

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

[color blue]Private Sub[/color] AddToWorksheet()

myExcelWorksheet.Range("C7:G11").Font.Bold = [color blue]True[/color]

myExcelWorksheet.Range("B2:H16").Value = "Hello World"

myExcelWorksheet.Range("D8:F9").WrapText = [color blue]True[/color]

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

[color blue]End Sub[/color]

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
....

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

thank you to all contributers!