Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Write Access Code To Add Excel Sheet

Posted on 2001-07-30
6
Medium Priority
?
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

721 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