Solved

Write Access Code To Add Excel Sheet

Posted on 2001-07-30
6
423 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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