Just a simple questions: can a worksheet be posted to a sharepoint site with a macro?

[GS7CLW]

We are going to be converting to sharepoint for report distribution.
My worksheets are set up for email distribution (one-click) -- will I
be able to do the same with Sharepoint?

Hope everyone has a great weekend!
(May be a LONGGGGGGGGGG weekend for some of us!~!!)

cliff

 

[mtskv5]

Cliff,

My experiance with SharePoint via marco is limited, but I've never been able to get much working between the two. Most of it reverts to a VB Script to interact.

 

[DanFerg]

It's very doable. I've written VBA to open, update links, resave, and save historical backup files.

Division = Cells(i, 2).Value
Target = Cells(i, 3).Value
Workbooks.Open Filename:="http://my.omnicare.com/private/teams/accounting/Payroll Related/" & Division & "/" & Target

where i is looping, Divison is a subfolder, and Target is the actual file name.

Dan.

 

[GS7CLW]

OK, I have mapped my sharepoint site to my computer (Z: drive),
my documents are stored in the Shared Documents folder; (1) filename
is floorreport.xlsm

So I would like to svae this file to
Z:\Shared Documents\FloorReport.xlsm
and then check it in.

I have tried activeworkbook.saveas "
Z:\Shared Documents\FloorReport.xlsm"
but I get a write failure message
and I have not even gotten around to trying to check it in using VBA.

Right now I using a batch file to write to the Z:| drive
then simple click the appropriate links on the sharepoint page to check it in.

I have also tried a couple of times with Kill to delete the old file first
then Filecopy to write the new file
but I still get the write failure error msg.

 

[DanFerg]

I originally mapped a drive letter to our SP site, but the VBA didn't care for it.

to get my path originally, i recorded a macro as i re-saved a spreadsheet to the site.

 

[GS7CLW]

yes, i also tried that angle and got a debug msg
everytime I tried to save it to the sp folder.

so far, the batch file works great ( I run IT using VBA)
(I also send an email using the same macro to notify
all concerned that it has been updated -- the vba email
includes a direct link to the report)

I just have to click the report and check it in right now.
which is not but 2 extra clicks.

have you ever tried to use VBA to check a report in on sp?

 

[DanFerg]

Check in or out? No. Not even an attempt. We don't bother with that, but rather use SP basically as a file server.

That may be an angle you toss around???
-Dan