New document - Rename

[Michael Morisoli]

Hello, I am new to Office vba but I would like to do the following. I am a
seasoned VB6 and .NET programmer, so coding is not new to me, just vba and
Office automation.



I would like to created a document template that automatically sets the name
of a new document to a formatted unique number. For example: the user
chooses new document and an onOpen event fires where my code goes out and
issues a SQL query to obtain the next higher number for that document
library. Then my code renames to file to "QA Document 00823.Doc" and saves
it.



Does this sound possible?



This is running under SharePoint Portal Server 2003 from a document library.
I would handle this in SPS, but its document handling events does not
provide any events to hook into until you save the document.



Thanks for any help, Mike

 

[Malcolm Smith]

Mike

Though I haven't yet used Sharepoint, though I have used no end of other
DMS (Document Management Systems) you could always do what I do.

When I create a document I do what is known as 'front end profiling'; in
other words it goes straight into the DMS.

You could have this done in one of the start-up templates to do something
like:

ActiveDocument.Save

Then to change the caption of the document all you have to do is to
interrogate the DMS, in your case SharePoint.

Now, this is where I get baffled. Doesn't Share Point give you the next
file name in any case? I know that other DMS work with the principle that
the user need not know nor care what the file name it as the document is
located either by searching or by the document number, subject or one of
the many categories.


You may have to give us a little more information here about the workings
of SharePoint. Am I to understand that SharePoint doesn't sort out the
filename itself but waits for Word to present it with a saved document
first?

Malc
www.dragondrop.com

 

[Michael Morisoli]

Malc, thanks for the fast response. Yes I was a bit vague about SharePoint
because it's potentially such a huge topic.



While I understand and agree with you that when you are using a true DMS,
the file name should not be significant, but in this case I am merely doing
what the customer is asking for. There are times we fight and times we just
so "ok". This is one of those times I just need to say Ok, I will try.



When a user asks to create a new document within SharePoint Portal Server
(SPS) it simply opens a user supplied template file with the standard temp
name like Document1.doc, Document2.doc . . .



The client currently uses a naming convention on the file share system and
wants to automate it with the move to SharePoint if possible. What they do
is use a system where the file name is broken into a few parts.
<Department> <Client> <Unique ID>. So a document may be something like
"QC-Ford-002345.doc".



The final goal would be to present the user with a custom form as soon as
the start to add a new document. The form would prompt with drop down list
boxes for the department and client while looking up the next unique ID from
a SQL database or via a call to a web service which ever is easier for Word.



For sake of thoroughness let me give a bit more background.



This is SharePoint Portal Server 2003 (SPS) with Office 2003. The situation
would be the same with the new Windows SharePoint Services (WSS) which is
now included free with Windows 2003 Server. SPS and WSS are both native
..NET applications and present the file system as a standard HTML file
system.



I have installed and just started playing with .NET for Office Tools where I
can start to write managed code for Office Applications.



It seems to me that the only way to set the name is going to be a SaveAs
call using the FQDN to the http:// sps site.



I am just not sure if I should try this using VBA or the new .NET object
model in Office 2003. If I use VBA, then it should run with any user, but I
don't know how to interact with SPS or SQL from a pure VBA environment. If
I do it in .NET, I have complete access to the SPS object model as well as
simple SQL access, but I am not sure how to do the form processing.



Any suggestions?



Mike

 

[Lars-Eric Gisslén]

Michael,

I would suggest you use VBA to create the file name for your document. If
you are familiar with VB there should be no problem for you to use VBA. VB
and VBA are so close you can often just copy/paste code between VB and VBA.
What you have to do is override the SaveAs() Word function in a VBA code
module. When you have created a Sub with the name SaveAs() you just put the
instertion point on the word SaveAs and hit F1 get the description of the
command.

We have set up a public SPS for our demo documents in Office 2003 (Word and
Infopath) so they can be accessed from just anywhere (mostly computer fairs
for the moment). The .Net solution for the Word documents are stored on
another IIS Server so they can also be accessed from just anywhere. The
problem is that the .Net 1.1 framework has to be installed on every client
machine. Further, each solution must be trusted on each client machin to be
installed and run. That means there is alot of overhead to just be able to
run a .Net solution on a client machine. This may be ok if the trusts are
managed by the administrators within a corporation and rolled out to all
client machines.

Regards,
Lars-Eric

 

[Michael Morisoli]

Lars-Eric, thanks for the info. You are right about .NET and the security
of the assembly, a bit too much for now.

Making the decision to go with VBA, what method would you recomend to access
a SQL database from VBA, or is there a way to access a web service?

Mike

 

[Lars-Eric Gisslén]

Mike,

We use ADO. If you connet to the SQL server from VB with ADO you should
almost be able to just copy/paste the code from VB to VBA and use it. If the
SQL Server is accesible through the Web you could use ADO/RDS

When we connect to a Web service we usually use the WinInet API. It takes
some code but I have written wrapper classes for the API calls for both the
HTTP and FTP protocolls. When we work with Web services we normally use ASP
services and that is quite straight forward as a normal HTTP call. .Net XML
Web services takes some more coding but still doable. We have choosen
WinInet to give us the control we want. I know there are some higher level
controls to use but that requires that those controls are installed on the
target machines and that is nothing I'm very fond of. It's ok for a VB
application when you can include them in the install script but that's not
normally the case with Word documents.

Regards,
Lars-Eric

 

[Michael Morisoli]

Perfect, let me dust off some of my old ASP code and dig into this thing.
It's funny, I have been coding in .NET for just a few years, but already VB
and ASP feel like a strangers to me. And after all the years we spent
together too.

Thanks for the VBA insight. I had completely forgotten that VBA is almost
exactly like ASP.

Thanks again for the help.

Mike

 

[Malcolm Smith]

Mike

I have some code somewhere which gets the next number from a database.
I have used it for invoices in the past. Perhaps this is what you
require?

Let me dig around and locate; I know that this should have gone onto the
site (the link is there though the code is not) but would this help?

This uses a database somewhere on the LAN to get the next number. I use
an Access database (well, I could have used SQL Server 2000 but for one
man this was a little overkill) and so all you would have to do is to
change the connection string.

Would this help?

- Malc
www.dragondrop.com

 

[Michael Morisoli]

Malcolm, thanks for the offer, but I was able to figure it out.

My code uses ADO to access a stored procedure which returns the next
document number which i zero pad and then save the document as.

Works fine in all my tests, but it will require some version of ADO exist on
each of the clients workstations. Haven't had a chance to test this on
various configurations yet.

Thanks to all that helped with this.

Mike