UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Clear Out Specific Spreadsheets Content Before Exporting From Access, Access 2010    
 
   
Jrayhan
post Nov 13 2017, 02:05 PM
Post#1



Posts: 612
Joined: 29-November 12



Good Afternoon UA,
I am trying to clear out 3 specific worksheets from a workbook before exporting 3 queries from Access using VBA. Basically, I have 3 output query results in access which I am exporting to a workbook using the following code..

CODE
Dim iFile As String
iFile = CurrentProject.Path & "\" & "Template\My Sales Report Template.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "OutputToReportWEEKLY", iFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "OutputToReportMONTHLY", iFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "OutputToReportFSCTTLs", iFile, True

This process was overriding the data on an existing tabs which is great, but it doesn't delete the old data first. It just adds records from row 1 and down depending on amount of recordset.
So if I had 30 rows from last week and this week I have only 20, it will paste those 20 rows from Row 1 to Row 20 and keep the last 10 rows from last week. Which is inaccurate data.

So I would like to delete the content for those 3 specific tabs first and then export those 3 queries. Those 3 tabs are linked to 3 different pivot tables.

I would appreciate if someone can help me regarding this matter..

Thanks in advance.
-Ray
This post has been edited by Jrayhan: Nov 13 2017, 02:26 PM
Go to the top of the page
 
Doug Steele
post Nov 13 2017, 02:46 PM
Post#2


UtterAccess VIP
Posts: 21,446
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Consider deleting the spreadsheets before repopulating them.

I'm assuming those aren't the only spreadsheets in the workbook (If they are, just delete the workbook)

Try code like:

CODE
Dim objExcel As Object
Dim objWorkbook As Object
Dim iFile As String
  
  iFile = CurrentProject.Path & "\" & "Template\My Sales Report Template.xlsx"
  
  Set objExcel = CreateObject("Excel.Application")
  Set objWorkbook = objExcel.Workbooks.Open(iFile)
  objExcel.Application.DisplayAlerts = False
  objWorkbook.Sheets("OutputToReportWEEKLY").Delete
  objWorkbook.Sheets("OutputToReportMONTHLY").Delete
  objWorkbook.Sheets("OutputToReportFSCTTLs").Delete
  objExcel.Application.DisplayAlerts = True
  objWorkbook.Close savechanges:=True
  Set objWorkbook = Nothing
  objExcel.Application.Quit
  Set objExcel = Nothing


--------------------
Go to the top of the page
 
Jrayhan
post Nov 14 2017, 12:51 PM
Post#3



Posts: 612
Joined: 29-November 12



Thanks for the suggestion! It worked!
Yes, there are other spreadsheets in that workbook, such as pivot tables. Hence, I cannot delete the entire workbook. :/

I do have a question. Your suggested code is deleting those 3 worksheets. But those worksheets are the sources for 3 different pivot tables in that excel file.
Is it going to create any issue for those pivot tables? I mean.. i just ran the process you have suggested and it deleted the worksheets and pivot looks fine.. I am just curious to know if there might be any issues I may come across down the road.

Would it be possible to clear out the content of the cells in those 3 worksheets? I wanted to know for learning purpose. smile.gif


Thanks again!
Ray
This post has been edited by Jrayhan: Nov 14 2017, 12:52 PM
Go to the top of the page
 
JonSmith
post Nov 14 2017, 01:23 PM
Post#4



Posts: 3,150
Joined: 19-October 10



I would have expected those pivots to break actually since you deleted the source.

Have you considered using a template file rather than constantly updating a 'dirty' one?

JS
Go to the top of the page
 
Doug Steele
post Nov 14 2017, 01:29 PM
Post#5


UtterAccess VIP
Posts: 21,446
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Jon's suggestion of using a template is a good one. However, to simply delete the data from the three spreadsheets, you can use

CODE
Dim objExcel As Object
Dim objWorkbook As Object
Dim iFile As String
  
  iFile = CurrentProject.Path & "\" & "Template\My Sales Report Template.xlsx"
  
  Set objExcel = CreateObject("Excel.Application")
  Set objWorkbook = objExcel.Workbooks.Open(iFile)
  objExcel.Application.DisplayAlerts = False
  objWorkbook.Sheets("OutputToReportWEEKLY").Cells.ClearContent
  objWorkbook.Sheets("OutputToReportMONTHLY").Cells.ClearContent
  objWorkbook.Sheets("OutputToReportFSCTTLs").Cells.ClearContent
  objExcel.Application.DisplayAlerts = True
  objWorkbook.Close savechanges:=True
  Set objWorkbook = Nothing
  objExcel.Application.Quit
  Set objExcel = Nothing

--------------------
Go to the top of the page
 
Jrayhan
post Nov 14 2017, 02:56 PM
Post#6



Posts: 612
Joined: 29-November 12



Thanks to you both!
Jon, that's a great idea. I could have all those 3 worksheets empty in the beginning of the process and after exporting to the excel I can do "save as" to a specific location or have the user decide where they want to save it. How would I create the SAVE AS process in this case? I would like to give user the option to choose where they want to save the file.

Here is the code I have so far.

CODE
Private Sub cmdRunAll_Click()

Dim iFile As String
Dim objExcel As Object
Dim objWorkbook As Object


iFile = CurrentProject.Path & "\" & "Template\My Sales Report Template.xlsm"


DoCmd.SetWarnings False


  Set objExcel = CreateObject("Excel.Application")
  Set objWorkbook = objExcel.Workbooks.Open(iFile)

  objExcel.Application.DisplayAlerts = False
  objWorkbook.Sheets("OutputToReportWEEKLY").Cells.ClearContents
  objWorkbook.Sheets("OutputToReportMONTHLY").Cells.ClearContents
  objWorkbook.Sheets("OutputToReportFSCTTLs").Cells.ClearContents
  objExcel.Application.DisplayAlerts = True
  objWorkbook.Close savechanges:=True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "OutputToReportWEEKLY", iFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "OutputToReportMONTHLY", iFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "OutputToReportFSCTTLs", iFile, True

  Set objWorkbook = Nothing
  objExcel.Application.Quit
  Set objExcel = Nothing

MsgBox "All done!", vbOKOnly

DoCmd.SetWarnings True
End Sub

This post has been edited by Jrayhan: Nov 14 2017, 02:57 PM
Go to the top of the page
 
Doug Steele
post Nov 14 2017, 03:27 PM
Post#7


UtterAccess VIP
Posts: 21,446
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You could call the Windows File Save API, or use the FileDialog object to prompt the user for the file name, then change the line of code

CODE
objWorkbook.Close savechanges:=True

to

CODE
objWorkbook.Close filename:=strFileName, savechanges:=True

(where strFileName stores the name of the file they selected)


--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th November 2017 - 07:59 AM