rename an excel workbook

  • Thread starter Thread starter Bill Reed via AccessMonster.com
  • Start date Start date
B

Bill Reed via AccessMonster.com

I am the beneficiary of some wonderful code for transferring an access
recordset to an excel workbook from the great Dev Ashish, but it is one
line short of perfection. Here's the code:

Dim objWkb As Workbook
Dim objSht As Worksheet

Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWkb = .Workbooks.Add
End With

When I tried to set objWkb.Name to a more descriptive name than "Book1", I
got a msg saying the property is read-only.
How do I change the name of the workbook?

Thanks,

Bill
 
Thanks a million.

The query that I want to use for the recordset contains parameters (not
references to controls on a form, but entries the user must make when the
query runs). I've used the widely distributed code below to try to get the
parameters into the recordset but I keep getting a msg, "...Planning.mdb
can't find the name 'blahblahblah' you entered into the expression."

Set db = CurrentDb
Set qdf = db.QueryDefs(strQueryName)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

When I debug.print the name in question, it looks just fine. Any
suggestions?
 
That is half the solution. Now you need to delete the origninal.
close the xlapp
use the kill statement to delete the old name
 
That is half the solution. Now you need to delete the origninal.
close the xlapp
use the kill statement to delete the old name
Click to expand...

Huh???

The OP just created a new workbook, with a default name (Book1.xls) that it
was never saved under. What "old name" does the OP supposedly need to
kill/delete?
 
You are correct. I had forgotten that, I was keying his statement about
changing the name.
 
Thanks to all for your generous help. Even the mistaken "Kill" advice
taught me something.

I have one more question though. The code below works great and I've got my
export routine humming, but it doesn't include headers which are a default
of the transfer spreadsheet macro. I seem to remember having to cycle
throught the .field(n).name's of the rs using cell offsets to get them in
the 1st row in a previous application. Any suggestions? Code samples?

With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs
.Name = strRptType & " Report"
End With

Thanks,

Bill
 
Back
Top