Excel宏和Sharepoint [英] Excel Macros and Sharepoint
问题描述
我们有一个用户编写了一个宏，当他运行它时，会将工作簿另存为.csv到网络文件共享上的父文件夹中.该宏为他们工作了很长时间，但现在他们承受着将这些电子表格放入其中的压力. 共享点.问题在于，当他从SP库打开工作簿，然后运行宏时，他得到了错误，(不足为奇).
此处是代码"他正在使用:
CreateObject("Scripting.FileSystemObject").Getfile(ActiveWorkbook.FullName).ParentFolder.ParentFolder.Path ;
他已将网络驱动器号映射到要使用的文件夹中，但是脚本似乎无法识别.
是否可以通过任何方式更改代码，使其能够识别http://whatever/path/opened/from，然后将其保存回库中的父文件夹中?
非常感谢.
泰德
P.S.顺便说一下，这是完整的代码.请注意，我不是开发人员.我不知道这是怎么工作的.
SaveCSVs宏中的代码为:
显式选项
Sub SaveCSVs()
'
'SaveCSVs宏
'在父目录中将所有突出显示的工作表另存为CSV文件.
'
'由Cassandra Kemp撰写，由Bill Fuhrmann修改
将newWks设置为工作表
昏暗的工作表
昏暗的ParentFolderName作为字符串
ParentFolderName = CreateObject("Scripting.FileSystemObject").Getfile(ActiveWorkbook.FullName).ParentFolder.ParentFolder.Path
对于ActiveWorkbook.Windows(1).SelectedSheets中的每个星期
'使用ActiveWorkbook.Worksheets保存所有工作表
'使用ActiveWorkbook.Windows(1).SelectedSheets仅保存突出显示的工作表.
wks.将复制到新工作簿
设置newWks = ActiveSheet
有了newWks
Application.DisplayAlerts = False
.SaveAs文件名:= ParentFolderName& "\" & wks.Name，FileFormat:= xlCSV
.Parent.Close savechanges:= False
结尾为
下一个星期
MsgBox最终生成的CSV:" " & ActiveWorkbook.Name
结束子
你好
应该与文件系统一起使用的提到的代码.一方面，Sharepoint doclib不是文件系统(因此，当您从浏览器中打开Excel文件时，该文件将无法工作)，但另一方面，它支持WebDAV协议，该协议允许例如打开Sharepoint doclib和文件夹 在Windows资源管理器中(尝试在Windows资源管理器中创建映射的网络驱动器，然后在此处指定Sharepoint doclib的URL).在这种情况下，它的行为类似于资源管理器中的文件系统，因此您可以尝试从那里打开Excel文件并尝试运行Macro.我不确定100％ 它会起作用，但值得尝试.
We have a user who wrote a macro that when he ran it, would save the workbook as a .csv to a parent folder on the network file share. That macro worked for them for a long time, but now they are getting pressure to put those spreadsheets into Sharepoint. The problem is that when he opens the workbook from a SP library, and then runs the macro, he get's errors, (not surprising).
Here is the "code" he is using:
CreateObject("Scripting.FileSystemObject").Getfile(ActiveWorkbook.FullName).ParentFolder.ParentFolder.Path
He had mapped a network drive letter out o the folder he wanted to use, but the script doesn't seem to recognize that.
Is there any way to change the code so that it would recognize an http://whatever/path/opened/from, and then save it back to the parent folder in a library?
Thanks much.
Ted
P.S. Here is the full code by the way. Note that I'm not a developer. I have no idea how any of this works.
The code in the SaveCSVs macro is:
Option Explicit
Sub SaveCSVs()
'
' SaveCSVs Macro
' Saves all highlighted sheets as CSV files in parent directory.
'
' Written by Cassandra Kemp, modified by Bill Fuhrmann
Dim newWks As Worksheet
Dim wks As Worksheet
Dim ParentFolderName As String
ParentFolderName = CreateObject("Scripting.FileSystemObject").Getfile(ActiveWorkbook.FullName).ParentFolder.ParentFolder.Path
For Each wks In ActiveWorkbook.Windows(1).SelectedSheets
'use ActiveWorkbook.Worksheets to save all worksheets
'use ActiveWorkbook.Windows(1).SelectedSheets to only save highlighted sheets.
wks.Copy 'to a new workbook
Set newWks = ActiveSheet
With newWks
Application.DisplayAlerts = False
.SaveAs Filename:=ParentFolderName & "\" & wks.Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks
MsgBox "FINISHED GENERATING CSV: " & ActiveWorkbook.Name
End Sub
hello
mentioned code supposed to be used with file system. From one side Sharepoint doclib is not file system (so it won't work when you open Excel file from browser) but from other it supports WebDAV protocol which allows e.g. open Sharepoint doclib and folders in Windows explorer (try to create mapped network drive in Windows explorer and specify url of Sharepoint doclib there). In this case it behave like file system in explorer so you may try to open Excel file from there and try to run Macro. I'm not 100% sure that it will work but it worth to try.
这篇关于Excel宏和Sharepoint的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
