saving spreadsheet

I have a program that creates spreadsheets based on extracted data.
This part works fine. I can create a new spreadsheet. What is the
best way to save the new spreadsheet. I do not seem to be able to
get common dialog to work and ActiveWorkbook.SaveAs doesn't work the
way I'd like. It should check to see if there is a current version
of the spreadsheet already saved.

Here is the code I'm currently working with:
Sub OpenRecordsetOutput(rstOutput As Recordset)
With rstOutput
I = 0
counter = 0

.MoveFirst

Set objXl = New Excel.Application
With objXl
.Visible = True

Set objWkb = .Workbooks.Add
Set objSht = objWkb.Worksheets(1)
objSht.Name = RTrim(Mid(cboState.Text, 4, 50)) & "-" & RTrim(Mid
(cboCnty.Text, 5, 50))

'objSht.Range("A1:J1").Interior.ColorIndex = 37
objSht.Range("A1:O2").Font.FontStyle = "Bold"
objSht.Range("A3:O13000").Font.FontStyle = "Regular"
objSht.Range("A1:O13000").Font.Name = "Comic Sans MS"
objSht.Range("A1:O13000").Font.Size = 9

With objSht

.Range("a1") = "Report of: " & Mid(cboState.Text, 3, 20) & Mid
(cboCnty.Text, 4, 30) & " Median Loan Amounts"
.Range("a2") = "Year"
.Range("b2") = "State"
.Range("c2") = "County"

If cbkTract.Value = False Then
.Range("d2") = "Med Loan Amount"
.Range("e2") = "# of Loans";
.Range("f2") = "County Name"
End If
If cbkTract.Value = True Then
.Range("d2") = "Tract"
.Range("e2") = "Med Loan Amount"
.Range("f2") = "# of Loans";
.Range("g2") = "County Name"
End If

row = 3
Do While Not rsRecs.EOF
For col = 0 To rsRecs.Fields.count - 1
objSht.Cells(row, col + 1) = "'" & rsRecs.Fields
(col).Value
Next col
row = row + 1
rsRecs.MoveNext
Loop

rowsout = row - 3 'Subtract 2 for headers
endmess = "JOB COMPLETE: There are: " & rowsout & "
records in this report. Use file/save as to save spreadsheet."
objSht.Cells(row, 1) = "'" & endmess
endrange = "a" & LTrim(Str(row))
objSht.Range(endrange).Font.FontStyle = "Bold"
objSht.Range(endrange).Font.ColorIndex = 3
fname = "c:Documents and SettingsC18850medlnamts" &
RTrim(Mid(cboState.Text, 4, 50)) & "-" & RTrim(Mid(cboCnty.Text, 5,
50) & ".xls")
ActiveWorkbook.SaveAs FileName:= _
fname, FileFormat:=xlNormal, Password:="";,
WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False


End With
End With

End With

End Sub

It works just not the way I'd like.
Thanks for any help.
Ronhvb03

Messages in this topic (1) Reply (via web post) | Start a new topic

.

Hi Ron,

> ...ActiveWorkbook.SaveAs doesn't work the way I'd like.
> It should check to see if there is a current version
> of the spreadsheet already saved.

You could use the FileLen() command to see if the file already exists,
or use a FileSystemObject to evaluate it for additional information
(file creation or updated date). You could also automate the process of
opening it and testing for cell data that would indicate whether the
file was the correct type to overwrite.

Of course, you could also save the files with an ISO8601 date formatted
name to indicate when it was created so that it's less likely to
conflict (yyyy-mm-dd.xls).

-Shawn

Messages in this topic (2) Reply (via web post) | Start a new topic

.