VBA Save File to SharePoint and Choose Property Type

Melimob

Active Member
Joined
Oct 16, 2011
Messages
396
Office Version
  1. 365
Hi

I had an excel file where one of the macros saved the file as a new workbook to the users last open folder.
I now want to save the new file in to a SharePoint library folder but as the user has to choose a content properties, its coming up with a debug error.
The content is a droplist of Region & Country. I thought I had this worked out after reading forums but still getting an error and would appreciate any advice on how to correct..

Original code:
VBA Code:
Sub SaveAsNewWkbook_New7()
    
    Application.ScreenUpdating = False
    
    RemoveFilters1a
        
    ThisFile = Sheets("Instructions").Range("G1").Value
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.path & Application.PathSeparator & ThisFile
    
    RemoveFormulas6
  
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    
    'Sheets("Send Email").Range("A1").Select
    
    
    MsgBox ("Workbook Created. Post to SharePoint & Notify PMs.")
    
    Protect
End Sub

Adapted code:
VBA Code:
Sub SaveAsNewWkbook_New7()
    
    Application.ScreenUpdating = False
    
    RemoveFilters1a
        
    ThisFile = Sheets("Instructions").Range("G1").Value
    ThisPath = Sheets("Instructions").Range("G2").Value
    ThisProperty = Sheets("Instructions").Range("G3").Value
    
    ActiveWorkbook.ContentTypeProperties("Region and Country").Value = ThisProperty
    ActiveWorkbook.SaveAs Filename:=ThisPath & ThisProperty & ThisFile & ".xlsm"
    
    RemoveFormulas6
   
    ActiveWorkbook.Save
    
    Application.ScreenUpdating = True
    
    'Sheets("Send Email").Range("A1").Select
    
    
    MsgBox ("Workbook Created. Post to SharePoint & Notify PMs.")
    
    Protect
End Sub

In case it matters: the values in Instructions are..
Path: G2 = https://teams.xxx.xxxxx.com/sites/PartnerTestTeamPIT/MoveTemplates/
Property: G3 = is a look up formula based on another field the user has selected to return Region and Country in the same format as what they would have had to select on SP.

Many thanks for any advice, I think I am close but yet so far as usual!
Melissa
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top