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
> Need To Export Xlsx And Overwrite - Without Kill, Access 2016    
 
   
awebnut
post Dec 6 2018, 10:23 AM
Post#1



Posts: 2
Joined: 6-December 18



My first post - so please go easy ;-)
I've scanned the forums for this subject and while some were similar in nature, they weren't quite what I'm addressing here. I need to export an Access query to an Excel workbook in a document library on SharePoint 2013 - and overwrite the existing file in the library. Because I'm dealing with specific permissions to the file on SharePoint, first killing the existing file is not an option as I'd have to redo permissions each time I upload a new version of the file. The code is very basic (below) - just using TransferSpreadsheet. I just find it surprising I'm not finding a way to simply overwrite a file. Thoughts?

Public Sub ExportFile()
On Error GoTo err_ExportFile

Dim strWorksheetPath As String
Dim strDriveLetter As String

strWorksheetPath = ""
strDriveLetter = ""
strWorksheetPath = "\\SomeSharePointPath\File-Master.xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "File-Master", strWorksheetPath, True
MsgBox "File-Master.xlsx exported to SharePoint", vbOKOnly

err_ExportFile:
Debug.Print Err.Number & ": " & Err.Description
Debug.Print Err.Source
End Sub
Go to the top of the page
 
DanielPineault
post Dec 6 2018, 10:37 AM
Post#2


UtterAccess VIP
Posts: 6,273
Joined: 30-June 11



Why not simply update the existing file?

Is the SharePoint library mapped to a drive?



--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 11:07 AM
Post#3


UA Admin
Posts: 34,084
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

I would imagine that you can delete existing worksheets--as appropriate--and then export the new worksheet to the existing workbook. Or perhaps do it the other way around, add the new worksheet and then delete the old one.

What I've found is that you can record a macro in Excel which does what you need to do, i.e. the deletion, and then recreate that in the Access VBA.

I've not done the deletion step, but I do insert new worksheets, along these lines. I'm sure there's also a way to delete.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
awebnut
post Dec 6 2018, 11:12 AM
Post#4



Posts: 2
Joined: 6-December 18



Thank you for the quick reply. In answer to your question, yes, the SharePoint site is mapped to a drive letter in Windows 10.
Go to the top of the page
 
kfield7
post Dec 6 2018, 11:17 AM
Post#5



Posts: 905
Joined: 12-November 03
From: Iowa Lot


I think George's suggestion of creating a new tab then deleting the old tab, then rename the new tab, is simplest.
If there's only one tab in the workbook, I'm thinking you can't delete the last remaining tab.
To just overwrite the existing data, you'd want to first delete the old data, otherwise you might have some 'leftover" data in there.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 11:34 AM
Post#6


UA Admin
Posts: 34,084
Joined: 20-June 02
From: Newcastle, WA


That's another good point, though.

If you define a range in the worksheet for the data, you can work with that range to delete any data in it and then put new data there with the CopyFromRecordSet() method in Access VBA.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2018 - 12:27 AM