VBA saving Excel to Sharepoint freezes forever with a screen showing “Getting list of available content types and properties…” -

i have vba that, along whole lot of other stuff, saves excel workbook sharepoint (enterprise 2010 think) , works fine of time every once in while, when user runs vba, excel freezes pop showing "getting list of available content types , properties...". if user selects cancel pop come "run-time error '1004': method 'saveas' of object '_workbook' failed. if user selects 'debug' last line of vba highlighted creating error.

dim filename string dim exceldirname string  filename = [c9] exceldirname = [c16] & "/"  thisworkbook.saveas exceldirname & filename & ".xls" 

since works (and worked on 6 months without happening) , not other times not sure , thinking updated in sharepoint.

i write bit differently, make more robust:

with somespecificsheet      dim path string     path = .range("savepath").value      dim filename string     filename = .range("savefilename").value  end  debug.assert trim(path) <> vbnullstring debug.assert trim(filename) <> vbnullstring  dim savepath string savepath = path & "/" & filename  thisworkbook.saveas savepath 

note:

• be explicit worksheet you're reading - you're reading whatever active sheet is, , unless every single worksheet in thisworkbook has expected values in $c$9 , $c$16, that's asking trouble.
• use named ranges, if user inserts column before column c or row before row 9, code still refers correct cells.
• let saveas determine file's extension.

• use debug.assert verify assumptions (and break before freeze). alternatively, can explicitly validate values, example:

  if path = vbnullstring or filename = vbnullstring      msgbox "i need path!"     exit sub end if