Overwrite same worksheet macro

[marstonk]

Hi,

I have a simple macro in access which sends new data to excel eachtime it runs. I want it to overwrite the existing data but I am having problems wioth this. It will only work if I delete the worksheet it generates in excel save close and then run the macro. Does anyone have a solution so that it overwrites the worksheet.

Thanks
Kate

 

[bzitt]

Here is some code that you can use to erase the existing contents of the worksheet and then put the new values in. If you are only wanting to overwrite certain cells use the Range function to identify them before clearing.

I have to give some credit to Dave Swanton, I took some of his code from
a previous post.

Public Sub DeleteXLWorksheet()

' Attribution to Dave Swanton for some of the code, from
' a previous post

Dim objXL As Excel.Application, wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set objXL = CreateObject("excel.application")

Set wb = objXL.Workbooks.Open("Your Filename goes here")

objXL.Visible = True

For Each ws In wb.Worksheets
If ws.Name = "Your Sheet name" Then
With wb.Sheets(ws.Name)
Cells.Select 'Select all of the cells
Selection.ClearContents 'Clear the contents
Selection.NumberFormat = "General" 'Reset all the formatting
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Range("a1").Select 'Move to upper left corner
End With

End If
Next ws

wb.Save

Set objXL = Nothing

End Sub

 

[marstonk]

Hi bzitt,

Thanks for your reply. The excel file I am using is password protected - do know the code to type the password in so it is not read only or anyway to override this when I run the macro.

Very much a beginner!!!

kitty

 

[bat17]

You could have a look at this thread
http://www.mrexcel.com/board2/viewtopic.php?t=102629

Peter

 

[Cbrine]

Set wb = objXL.Workbooks.Open("Your Filename goes here")

One of the parameters of this command is password. To open a password protected file, you will need to provide this value. Check the Excel VBA help on the Open method for details.
HTH.