Overwrite excel with new data from access (1 Viewer)

JMarie

Registered User.
Local time
Yesterday, 20:12
Joined
Aug 15, 2015
Messages
19
Hi all, I am hoping this is a simple fix.
I used some vb code to kick out data to my excel file. Basically each time the code is run, the new data overwrites the previous data on the excel sheet.
This works, it overwrites, BUT it doesn't clear out the previous information.

So say I ran it yesterday and there were 50 rows kicked out.
Then today I ran it again and there were only 10 rows kicked out.
My excel sheet overwrites the first 10 rows, but leaves the other 40.

Is there a way to make this clear out the whole page, then write the new data? I pretty much used the code from this site.

"https://btabdevelopment.com/export-a-table-or-query-to-excel-specific-worksheet-on-specific-workbook/"]https://btabdevelopment.com/export-a-table-or-query-to-excel-specific-worksheet-on-specific-workbook/"

I've also attached my code. Any ideas would be great. Thanks so much!
Marie
 

Attachments

  • Public Function SendToExelDiscipline.docx
    12.7 KB · Views: 57

Cronk

Registered User.
Local time
Today, 11:12
Joined
Jul 4, 2013
Messages
2,770
I create a new spreadsheet. Delete the earlier one if necessary.

However to clear an existing worksheet

ApXL.cells.select
ApXL.selection.clearContents
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:12
Joined
May 7, 2009
Messages
19,169
You may also use:

...
...
Set xlWSh = xlWBk.Worksheets(strSheetName)

xlWSh.Activate
XlWSh.UsedRange.Clear
...
...
 

JMarie

Registered User.
Local time
Yesterday, 20:12
Joined
Aug 15, 2015
Messages
19
Thanks to both of you. Cronk, your solution worked, until we started pounding on the form and doing several months in a row. (The reason I don't make new spreadsheets is this is linked to overwrite a txt file that needs sent for govt reports).

Arnelgp, I came up with what you sent, but only found it because of Cronk's lead.
Thanks to you both
 

Users who are viewing this thread

Top Bottom