Write Access Code To Add Excel Sheet

Hi,

I'm having some problems getting the code right, so I'll give points for whoever can write it for me.

I'd like some code behind an Access Button that will connect to an existing Excel File, check to see if a particular sheet is already present in the Excel file, and create it if it doesn't exist, or overwrite it if it does exist.  The sheet I'm adding can just be blank, as I'll then add code to write values to the cells.  Then, save the Excel changes and drop the connection to the sheet.  

I don't want to see any messages asking "Are you sure you wish to overwrite the sheet", etc.

Thanks, and Good Luck,

I'll award an additional 100 points to anyone who can direct me to a web site with shows some good examples of writing access code to interact with Excel Files. None of the text books I've read give any instruction on this...

ssteeves

LVL 1
ssteevesAsked:
Who is Participating?
 
carruinaCommented:
Insert the reference  "Microsoft Excel 8.0 Object Library"
and paste this code

The code open a Book named "C:\ExcelBook.xls"
select or insert a sheet named "Libro2"
Insert "Hello" in the cell 5,5
And save the file

Private Sub Comando0_Click()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim aux As Excel.Worksheet
Dim exists As Boolean
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\ExcelBook.xls") 'Open
'If you want use a preformated book change the method Add to Open (path & "\name.xls")

For Each aux In xlBook.Worksheets
    If aux.Name = "Libro2" Then
        Set xlSheet = aux
        exists = True
        Exit For
    End If
Next

If Not exists Then
    Set xlSheet = xlBook.Worksheets.Add
    xlSheet.Name = "Libro2"
End If
xlApp.Visible = True

'Code to insert values

xlSheet.Cells(5, 5) = "Hello"

xlBook.Save

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub
0
 
isondCommented:
steeves,

You might find this site interesting.

http://www.excel-vba-access.com/vba-excel-tips-intro.htm

Darren.
0
 
ssteevesAuthor Commented:
carruina,

The only problem I see is that I need to overwrite the sheet if it exists.  If I add the sheet with the same name, it gives me an error, and if I run code to delete the sheet first, it gives me a message asking if I'm sure I wish to delete the sheet.  I don't want to see that message.

Do you know a solution for this?

ssteeves
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ssteevesAuthor Commented:
isond,

Thanks for the link, but it wasn't what I was looking for.  I know some VBA For Excel, and I have a text book on it, and I know VBA For Access very well.  What I'm looking for though, is some VBA for Access code that gives examples of interacting with an Excel File.
0
 
carruinaCommented:
You can set the propierty "DisplayAlerts" to False


If Not exists Then
    Set xlSheet = xlBook.Worksheets.Add
    xlSheet.Name = "Libro2"
Else
    xlApp.DisplayAlerts = False
    xlSheet.Delete
    xlApp.DisplayAlerts = True

    Set xlSheet = xlBook.Worksheets.Add
    xlSheet.Name = "Libro2"
End If
0
 
ssteevesAuthor Commented:
That's perfect.  Thank you.
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.

All Courses

From novice to tech pro — start learning today.