Export and save into excel (1 Viewer)

[jarheadjim]

I tried searching the forums and haven't come up with anything that sounds like what i'm looking for. I have a VERY simple database that has a list of personnel and whether or not they are available for an event. I would like to have a command button on the form where I change the availability that will export the selected personnel to an Excel file and (if possible) save it with todays date as the name of the spreadsheet. If anyone can point me in the right direction I would appreciate it

 

[Matt Greatorex]

The following opens a blank Excel file (or a template, if you want to create one) and populates it with the data from a query.

I tried using code to open a new Excel process each time, but I kept getting 'rogue' processes left over after everything had finished. Unfortunately, noone on the forum was able to tell me how to get rid of them, but this method works fine.

If you do a bit of reading around formatting the spreadsheet, once opened, via VBA, you can just reuse the same blank sheet and there is no need to create any templates.

Hope it makes sense.

Dim myrecs as Recordset
Dim strSQL as string
Dim strTitle as String
Dim str_New_File as string
Dim CellRef as Integer
Dim str_Location as string

strSQL = whatever the query is
strLocation = filepath to blank Excel file or template
strTitle = name of Excel file

Set myrecs = CurrentDb.OpenRecordset(strSQL)

If myrecs.EOF Then
MsgBox "No records found matching the specified criteria"
GoTo End_Function
End If

'Open the relevant Excel template
'--------------------------------
Set ExcelBook = Workbooks.Open(str_Location & str_Title)
Set ExcelSheet = ExcelBook.Worksheets(1)

CellRef = 7
If li_Type = 1 Then

With myrecs
.MoveFirst
ExcelSheet.Range("A3") = Date

Do While Not .EOF
With ExcelSheet
.Range("A" & CellRef & "").Value = myrecs![something]
.Range("B" & CellRef & "").Value = myrecs![something 2]
etc.
End With
CellRef = CellRef + 1
.MoveNext
Loop
End With

str_New_File = new file name & ".xls"

ExcelBook.Application.DisplayAlerts = False
ExcelBook.SaveAs str_New_File
ExcelBook.Application.Quit

 

[jarheadjim]

I'll give that a try, thank you very much.

 

[Milothicus]

one way to avoid rogue instances is in your objects. anything you 'set' to something, 'set' to 'nothing' at the end of your code, in reverse order, so at the end of your code, i would add:

set ExcelSheet = nothing
set Excelbook = nothing
set MyRecs = nothing

it may stop leaving the instances in memory.

 

[f_disk]

hey matt

Matt....

How would I get your script to work from a Query ?
I want to take the information I get from a Query in
Access and transfer it to and Excel spreadsheet or
workbook and then when the excel sheet opens it
has predefined calculations on it related to the incoming
data....