Solved

Write Access Code To Add Excel Sheet

Posted on 2001-07-30
6
418 Views
Last Modified: 2006-11-17
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

0
Comment
Question by:ssteeves
  • 3
  • 2
6 Comments
 

Expert Comment

by:isond
ID: 6334308
steeves,

You might find this site interesting.

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

Darren.
0
 
LVL 3

Accepted Solution

by:
carruina earned 200 total points
ID: 6334367
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
 
LVL 1

Author Comment

by:ssteeves
ID: 6334488
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:ssteeves
ID: 6334519
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
 
LVL 3

Expert Comment

by:carruina
ID: 6334688
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
 
LVL 1

Author Comment

by:ssteeves
ID: 6335244
That's perfect.  Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now