Delete contents within an Excel WB using ACCESS VBA

[TechInNeed]

Having a problem here.
Is there a way to overwrite an excel file completely with access vba coding?
Basically my program behaves like this. When I click on a "Print" cmd button it outputs the search criteria data from an access DB that the user specifies, and stores the data in an *.xls file. Let's just say that 10 rows of data was output to a file called test.xls. Now when the user searches more data and outputs it to the same file with let's say 3 records. It will overwrite the first 3 records that already exists, but leaves the other records in the file. I want to be able to overwrite the entire file so that when the user outputs the 3 rows in that same file, then the other records are also overwritten with blank lines.

I'm thinking of either deleting all contents from the *.xls file and then populating the output OR deleting the *.xls file, creating it again, and then output the data.

Please comment.
All help is appreciated.
Thanks

 

[SBendBuckeye]

Try something like this. You could use a worksheet object or a workbook object as well if needed.

ActiveSheet.Cells.Select 'Select everything in sheet
Selection.Delete 'Delete everything in sheet
ActiveSheet.Range("A1&quot.Select 'Select cell A1

Good Luck!

 

[TechInNeed]

Thanks alot for the prompt response.
Appreciate it.
Also, what is the code to flush and close the excel app completely?
ie. when I run the cmd button to output the data into an excel file the first time, Excel.exe is still running in the task manager. Which in turns throws an error when the command is pressed a second time. I needed to close excel and flush the buffer.

Thanks

 

[SBendBuckeye]

post your code to save the workbook so I can see how you are doing it.

Good LucK!

 

[GeekGirlau]

Two steps:

Quit the application;
Set your object to Nothing.

For example (assuming that objExcel has been dimensioned as an Excel.Application object):

objExcel.Quit
set objExcel = Nothing

 

[TechInNeed]

GeekGirlau
I have tried this and it does not work

SBendBuckeye
Here is the code

Dim iCounter As Integer
Dim RecordCounter As Integer
RecordCounter = 1

Dim iCol As Integer
Dim iRow As Integer
iRow = 1

Dim ExcelCounter As Integer
ExcelCounter = 2
Dim ExportToExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Dim KillFile As String

KillFile = "C:\AccessDB\Print.xls"

If Len(Dir$(KillFile)) > 0 Then
Kill KillFile
End If

Set ExportToExcel = New Excel.Application
ExportToExcel.Visible = True
Set objBook = ExportToExcel.Workbooks.Add
Set objSheet = objBook.Worksheets(1)

objSheet.Application.Cells(1, 1).Select
objSheet.Application.Cells(1, 1).Value = "Heading 1"
objSheet.Application.Cells(1, 2).Value = "Heading 2"
objSheet.Application.Cells(1, 3).Value = "Heading 3"
objSheet.Application.Cells(1, 4).Value = "Heading 4"
objSheet.Application.Cells(1, 5).Value = "Heading 5"
objSheet.Application.Cells(1, 6).Value = "Heading 6"
objSheet.Application.Cells(1, 7).Value = "Heading 7"
objSheet.Application.Cells(1, 8).Value = "Heading 8"
objSheet.Application.Cells(1, 9).Value = "Heading 9"
objSheet.Application.Cells(1, 10).Value = "Heading 10"

iCounter = lstSearchResults.ListCount
Do While (RecordCounter < iCounter)

iCol = 0

objSheet.Application.Cells(ExcelCounter, 10).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 1).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 2).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 3).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 9).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 5).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 6).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 7).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 8).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1
objSheet.Application.Cells(ExcelCounter, 4).Value = Trim(UCase(lstSearchResults.Column(iCol, iRow)))
iCol = iCol + 1

iRow = iRow + 1
ExcelCounter = ExcelCounter + 1

RecordCounter = RecordCounter + 1

Loop


ActiveWorkbook.SaveAs Filename:=&quot;C:\AccessDB\Print.xls&quot; & &quot;.&quot; & &quot;xls&quot;, FileFormat:=xlNormal, Password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, ReadOnlyRecommended:=False, CreateBackup:=False

objSheet.Application.Quit
Set objSheet = Nothing
Set objBook = Nothing
Set ExportToExcel = Nothing

MsgBox &quot;You can access the print out in C:\AccessDB\Print.xls&quot;, vbInformation

Exit Sub

dberror:
MsgBox &quot;There was an error adding the record.&quot; & Err.Number & &quot;, &quot; & Err.Description

End Sub

 

[TechInNeed]

oops
also,
I substituted
objSheet.Application.Quit
with
ExportToExcel.Application.Quit

and it still behaves the same way.

 

[SBendBuckeye]

Looks like your code should work. You might try this instead:

objBook.Close
Set objSheet = Nothing
Set objBook = Nothing
ExportToExcel.Quit
Set ExportToExcel = Nothing

Good Luck!

 

[TechInNeed]

I changed the code up quite a bit to make it more efficient an it works fine now. I also narrowed the problem to be in the saving of the file.

ActiveWorkbook.SaveAs Filename:=&quot;C:\AccessDB\Print.xls&quot; & &quot;.&quot; & &quot;xls&quot;, FileFormat:=xlNormal, Password:=&quot;&quot;, WriteResPassword:=&quot;&quot;, ReadOnlyRecommended:=False, CreateBackup:=False

I solved this by perofmorming a normal save as opposed to a saveas

Also, this thread and preceeding threads have proven to be very helpful

http://www.tek-tips.com/viewthread.cfm?spid=707&newpid=707&sqid=122100