Copying File to Sharepoint 1

[xrayr]

I've found and have used some VBA coding to zip an Excel file. It works well.

I now want to copy that zipped file to a sharepoint.

I have coding to save a current workbook to a sharepoint, folder, etc. I do NOT have coding to call up the file I just zipped and move that newly zipped file to a sharepoint.

Can anyone please advise as to how I could call up the file I have just zipped and move that newly zipped file to Sharepoint?

Below is the coding for zipping the file using VBA.

Thank you!

Brandon


Sub Zip_ActiveWorkbook()
Dim strDate As String, DefPath As String
Dim FileNameZip, FileNameXls
Dim oApp As Object
Dim FileExtStr As String

DefPath = "C:\Documents and Settings\YOURFOLDER" '<< Change
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Create date string and the temporary xl* and Zip file name
If Val(Application.Version) < 12 Then
FileExtStr = ".xls"
Else
Select Case ActiveWorkbook.FileFormat
Case 51: FileExtStr = ".xlsx"
Case 52: FileExtStr = ".xlsm"
Case 56: FileExtStr = ".xls"
Case 50: FileExtStr = ".xlsb"
Case Else: FileExtStr = "notknown"
End Select
If FileExtStr = "notknown" Then
MsgBox "Sorry unknown file format"
Exit Sub
End If
End If

strDate = Format(Now, " mm-dd-yyyy")

FileNameZip = DefPath & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & ".zip"

FileNameXls = DefPath & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & FileExtStr

If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then

'Make copy of the activeworkbook
ActiveWorkbook.SaveCopyAs FileNameXls

'Create empty Zip File
NewZip (FileNameZip)

'Copy the file in the compressed folder
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameZip).CopyHere FileNameXls

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(FileNameZip).items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
'Delete the temporary xls file
Kill FileNameXls

'-------------------------------------------------------------



MsgBox "Your Backup is saved here: " & FileNameZip

Else
MsgBox "FileNameZip or/and FileNameXls exist"

End If
End Sub

 

[PHV]

I have coding to save a current workbook to a sharepoint
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xrayr]

I've been using the below coding to copy unzipped, open Excel workbooks to a Sharepoint:

buildSaveDest = "

http://YOURSHAREPOINTADDRESS/"

& ActiveWorkbook.Name 'Build Save As dest
Application.ActiveWorkbook.SaveAs buildSaveDest 'Save current File to SharePoint

 

[xrayr]

I've been trying to get the below coding to work for moving the newly zipped file to a sharepoint, but I keep getting the error message that the path to the sharepoint doesn't exist. The path works fine for the unzipped, open Excel file...

Anyone have any ideas on what could be the hangup?



Sub Copy_Certain_Files_In_Folder()
'This copies all ZIP files from FromPath to ToPath.
'Note: If the files in ToPath already exist it will overwrite
'existing files in this folder
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "C:\Documents and Settings\YOURFOLDER" '<< Change
ToPath = "

http://YOURSHAREPOINTADDRESS/"

'<< Change

FileExt = "*.zip*" '<< Change
'You can use *.* for all files or *.doc for word files

If Right(FromPath, 1) <> "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "You can find the files from " & FromPath & " in " & ToPath

End Sub

 

[PHV]

Unfortunately, fso don't work with web folders.

 

[xrayr]

:-(

Is there any other method to move the newly zipped file?

 

[PHV]

Have you tried this ?
oApp.Namespace("

http://YOURSHAREPOINTADDRESS/").CopyHere

FileNameZip

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xrayr]

I'm having issues with the coding using "oApp.Namespace("

http://YOURSHAREPOINTADDRESS/").CopyHere

FileNameZip".

The problem I'm having is with the FileNameZip. I keep getting an "object required" error message.

How can I define FileNameZip as any file with the extension of ".zip*"?

 

[xrayr]

I got it!

The solution was as simple as using the Explorer View path address to the Sharepoint folder:

\\parentsharepoint.com\subfolder\subsubfolder

It works great!!!

Thanks for your help!