• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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

0
ssteeves
Asked:
ssteeves
  • 3
  • 2
1 Solution
 
isondCommented:
steeves,

You might find this site interesting.

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

Darren.
0
 
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
 
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now