export to excel and save

[Pendragon]

Access03/WinXP

I am using the following to export a recordset to Excel; this works without
issue. The problem is that the workbook is not being saved as directed in
the code - the Excel window still shows "BookX" where X is the index for the
most recently created workbook.

I'm not using a Close or Quit function because after clicking a button to
create the export, it's necessary to direct the user to the Excel spreadsheet
for further data manipulation. I would like to have the filed saved
programmatically with a pre-defined file name.

All suggestions appreciated.

With objExcel
.Visible = True
.Workbooks.Add
.ActiveSheet.Name = CStr("CustomerInfo")
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1) = "'" & rs.Fields(i).Name
Next i
.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold = True
.Selection.Range("A2").CopyFromRecordset rs
.Range(.Cells(1, 1), .Cells(rs.RecordCount + 1,
rs.Fields.Count)).Columns.AutoFit
.Workbooks(1).SaveAs stDocName
End With


BTW, is there a way to programmatically retrieve the workbook index from
"BookX"? In other instances where I have used a Close command, e.g.,
..Workbooks(1).Close False, , False, it seems like the wrong workbook gets
closed. In creating a new workbook, isn't that given index #1 before all
other open workbooks?

 

[Klatuu]

Hard to give you an answer when neither Bookx nor the Close method is in the
posted code.

What is the purpose of the Cstr function here?
.ActiveSheet.Name = CStr("CustomerInfo")

No need to convert to a string, it already is a string.

Since we don't have all your code, this may be valid, but I see only one
workbook open:
.Workbooks(1).SaveAs stDocName

If this is the only workbook, then the (1) isn't necessary.

If you want to allow the user to select a directory and file name, The code
at this site is a very good way to do that:

http://www.mvps.org/access/api/api0001.htm

 

[Pendragon]

I figured out a solution using ActiveWorkbook.Name. I added Dim objWkbk as
String to my declarations and then modified the code:

With objExcel
.Visible = True
.Workbooks.Add
.ActiveSheet.Name = CStr("CustomerInfo")
objWkbk = ActiveWorkbook.Name
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1) = "'" & rs.Fields(i).Name
Next i
.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold = True
.Selection.Range("A2").CopyFromRecordset rs
.Range(.Cells(1, 1), .Cells(rs.RecordCount + 1,
rs.Fields.Count)).Columns.AutoFit
.Workbooks(objWkbk).SaveAs stDocName
End With

This avoids any problems with indeces on Excel workbooks by expliciting
designating on which workbook to take action.

Using ActiveWorkbook.Name would also allow one to extract the index number
of the workbook since all new workbooks begin with "Book".

Dim i as Long
i = Val(Mid$(objWkbk, 5, len(objWkbk)))

Of course, most indeces won't be more than 2 digits unless there are a whole
lot of Excel workbooks being opened.

 

[Pendragon]

Thanks for the reply, Dave. I was just posting my resolution when your
notification came through.

BookX is the name of the Excel workbook - result of .Workbooks.Add. The
assumption was that this newly created workbook automatically became the
first index in the Excel windows list - an incorrect assumption, I learned by
trial and error. This is no longer an issue since I am now using
ActiveWorkbook.Name as posted in my other reply.

The Close method is not in the code because the intent to is automatically
direct the user to the exported data in Excel.

Thanks for the info on .ActiveSheet.Name already being a string. This code
is a simple modification of something I found in these forums and that
particularly line is a direct copy. I gather that .ActiveSheet.Name =
"CustomerInfo" would be sufficient.

Thanks again for the reply. Always appreciated.