Document Creation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Grant Reid - 30 Jun 2005 08:52 GMT

Hi

Realy hope someone out there can help. I'm busy with a proof of concept at a
client, an investment bank, who has a requirement to automate the monthly
distribution reports from within Word (Word 2003). I have done some VBA work
years back but that was Excel specific, so I'm pretty clueless, hence my cry
for help.

My first objective is to write some code within a document that does the
following when a user clicks a button;

[1] Connect to MS SQL database and query a table called "Fund".
[2] Create a folder, named "Reports_Out" within the current directory if it
does not already exist.
[3] Create and save a copy of the current document within the "Reports_Out"
directory for each Fund with the following naming convention
"<FundName>_<CurrentDate>.doc"

Any help with coding a solution to this would be much appreciated.

Kind Regards - Grant

Reply to this Message

Jezebel - 30 Jun 2005 09:32 GMT

Other than actually writing it for you, it's hard to see how to answer this.
There's nothing tricky about coding it, and most of the code would be
exactly the same if you were doing it in Excel. If I were quoting it as a
professional job, I'd allow half a day for initial coding, and another two
or three for finding out what the client *really* wanted.

> Hi
>
[quoted text clipped - 22 lines]
>
> Kind Regards - Grant

Reply to this Message

Grant Reid - 30 Jun 2005 12:27 GMT

Hi

I've cobbled together some code which pretty much satisfies my initial
requirement, but there are some issues which I hope someone can shed some
light on.

[1] I'm able to connect to the database
[2] I'm able to create the folder "Reports_Out" if it does not exist
[3] I'm able to create a copy of the document with the required naming
convention within the "Reports_Out" folder

but........

I want the original document to remain open as the script runs, creating the
new files and then closing them. What is happening now is that as the script
runs, the original document closes. Can someone shed some light on this and
perhaps point out what it is that I'm doing wrong and possibly suggest how I
can accomplish this within my Loop. Any help would be much appreciated. See
script below.

Kind Regards - Grant

Sub CreateReports()
Dim FSO As FileSystemObject
Dim NewDir As String
Dim newName As String

Dim rs As New ADODB.Recordset
Dim sConnectString As String
Dim sSQL As String
Dim sDSN As String
Dim sUID As String
Dim sPWD As String

Set FSO = CreateObject("Scripting.FileSystemObject")
NewDir = ActiveDocument.Path & "\Reports_Out"

sDSN = "GRANTNTSRV - SIM"
sUID = "sa"
sPWD = "sybase"
sConnectString = "Data Source=" & sDSN & ";UID=" & sUID & ";PWD=" & sPWD
& ";"
sSQL = "SELECT Fund FROM ""Fund"""

'Check for existance of "Reports_Out" Folder
'and create if it doesn't exist
If Not FSO.FolderExists(NewDir) Then
FSO.CreateFolder NewDir
End If

'open a recordset
rs.Open sSQL, sConnectString

Do While Not rs.EOF
With ActiveDocument
'Save current document
.Save
newName = rs.Fields("Fund")
newName = newName & " " & Format(Now, "ddd, dd mmm yyyy") &
".doc"
.SaveAs FileName:=NewDir & "\" & newName
'.Close
End With
'access next record
rs.MoveNext
Loop

End Sub

> Hi
>
[quoted text clipped - 17 lines]
>
> Kind Regards - Grant

Reply to this Message

Jezebel - 01 Jul 2005 00:09 GMT

The issue is this: you start with an open document, then save it repeatedly
under new names. It's not that the original document closes, just that on
exit you're left with the last name you saved it under. Simplest fix would
be to re-open the original at the end of the routine.

Separately, you're saving each document twice, which is obviously
unnecessary. And your code will throw an error if the recordset is empty,
but maybe that never happens.

> Hi
>
[quoted text clipped - 98 lines]
>>
>> Kind Regards - Grant

Reply to this Message