Clear Contents In Excel & Transfer Sprdsht (1 Viewer)

[HeelNGville]

Thanks in advance for any assistance!

I currently have a DB in which a "transferspreadsheet" function is called and 2 tables are transferred to a "template" workbook in MS Excel named PM Upload. The tables transferred to Excel are (1) Listings & (2) Details. This is fine.

What I am struggling with is this. Prior to the transferspreadsheet function, I need to clear the contents of both worksheets (Listings & Details) & then save/close the excel file.

Through Access, how can I open & bypass the "enable macros" message, clear contents of both worksheets (Details & Listings) and then proceed with transferspreadsheet function.

Any help would be GREATLY appreciated.

 

[Guus2005]

You could simply delete the worksheets and create new ones. This is a quick method and this way you make sure that everything was deleted. However if you need the formulas's on this sheet, you can select the complete sheet (Cells.Select) and delete the range (Selection.ClearContents).

The only way you can bypass the "enable macro's" message is lowering the security level of your Access macro's.

Enjoy!

 

[HeelNGville]

Thanks for the reply. Unfortunately, I cannot delete as the spreadsheet that I am transferring to contains code that performs multiple functions once the data is transferred. Any other suggestions?

 

[Guus2005]

Thanks for the reply. Unfortunately, I cannot delete as the spreadsheet that I am transferring to contains code that performs multiple functions once the data is transferred. Any other suggestions?

Read the "However" part.

 

[HeelNGville]

Yes, got that. However, being somewhat "green" with VB, I was uncertain how to clear contents of both worksheets.

The following works well for a single sheet, however I am not certain how to incorporate VB language to clear data from both worksheets.

Thanks in advance.

Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlwrkBk = xlApp.Workbooks.Open("C:\My Documents\PM Upload.xls")

Set xlSheet = xlwrkBk.Worksheets("Details")

xlSheet.Rows("1:30000").Delete

xlwrkBk.Save
xlwrkBk.Close

Set xlSheet = Nothing
Set xlwrkBk = Nothing

xlApp.Quit

Set xlApp = Nothing

MsgBox "Done"

End Sub

 

[Guus2005]

Try this.

    Dim xlApp As New Excel.Application
    Dim xlwrkBk As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    Set xlwrkBk = xlApp.Workbooks.Open("C:\My Documents\PM Upload.xls")
    
    Set xlSheet = xlwrkBk.Worksheets("Details")

    xlSheet.Cells.Select
    xlApp.Selection.ClearContents
    
    xlwrkBk.Save
    xlwrkBk.Close
    
    Set xlSheet = Nothing
    Set xlwrkBk = Nothing
    
    xlApp.Quit
    
    Set xlApp = Nothing
    
    MsgBox "Done"

End Sub

 

[HeelNGville]

Received "Compile Error, User Defined type not defined".

Thanks for your help, I will approach this from a different angle. Just thought it would be efficient to automate as much as possible.

Thanks again.

 

[HeelNGville]

Just a thought, but is it possible to create a function in Access that would allow the Excel template to be copied/pasted?

This would allow for a "blank" formatted template to exist, then the code would be directed to the path that the template was stored, copy it and paste with a new name. Once completed, the transferspreadsheet would run and transfer data into new workbook.

Is this a possibility?

 

[Guus2005]

Here's my sample database.
It works.
Check your references.

 

[Guus2005]

Just a thought, but is it possible to create a function in Access that would allow the Excel template to be copied/pasted?

This would allow for a "blank" formatted template to exist, then the code would be directed to the path that the template was stored, copy it and paste with a new name. Once completed, the transferspreadsheet would run and transfer data into new workbook.

Is this a possibility?

Yes, you can try that as well.

 

[HeelNGville]

Can you share the code for the copy/paste functionality? Assuming the file you are copying is:

C:/My Documents/Test.xls

New pasted file would be
C:/My Documents/Test2.xls

Thanks again!

 

[HeelNGville]

Disregard. I have it.