ssteeves
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
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.
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
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
ASKER
That's perfect. Thank you.
You might find this site interesting.
http://www.excel-vba-access.com/vba-excel-tips-intro.htm
Darren.