Export to Excel

[misschanda]

Hello,
I got this code from http://www.mvps.org/access/forms/index.html that allows
form search. the form than, has a command that allows for export of the
filter information. my question is.. that when asked to choose location, i
chose excel 3 for example... the file is not exporting to destination.. any
help is appreciated

misschanda


Private Sub cmdExport_Click()
On Error GoTo ErrHandler
Dim arrCtl As Control
Dim intUbound As Integer
Dim intLbound As Integer
Dim intCount As Integer
Select Case cmdExport.Tag
Case "Choose"
intCount = -1
For Each arrCtl In Me.Controls
Select Case arrCtl.ControlType
Case acTextBox, acComboBox, acCheckBox, acListBox, acCommandButton
If arrCtl.Name <> "cmdExport" And arrCtl.Name <> "lstResult" Then
intCount = intCount + 1
ReDim Preserve arrCtls(0 To intCount)
With arrCtls(intCount)
.Name = arrCtl.Name
.Enabled = arrCtl.Enabled
End With
arrCtl.Enabled = False
End If
End Select
Next

With lstResult
.ColumnCount = 4
.ColumnWidths = "0,0,0"
.RowSourceType = "Value List"
.RowSource = "-1,-1,-1,Export Type," _
& "0,0,.xls,Excel 3," _
& "0,6,.xls,Excel 4," _
& "0,5,.xls,Excel 5," _
& "0,5,.xls,Excel 7," _
& "0,8,.xls,Excel 97," _
& "0,2,.wk1,Lotus WK1," _
& "0,3,.wk3,Lotus WK3," _
& "0,7,.wk4,Lotus WK4," _
& "0,4,.wj2,Lotus WJ2 (Japanese)," _
& "1,2,.txt,Delimited Text," _
& "1,8,.html,HTML"
'& "1,3,.txt,Fixed Length Text,"
.Selected(1) = True
End With
Label16.Caption = "Select ..."
cmdExport.Tag = "Export"
Case "Export"
If MsgBox("Are you sure you want to export this query", vbYesNo +
vbQuestion) <> vbNo Then
Call ExportRoutine
End If
intLbound = LBound(arrCtls)
intUbound = UBound(arrCtls)
For intCount = intLbound To intUbound
With arrCtls(intCount)
Me(.Name).Enabled = .Enabled
End With
Next
Label16.Caption = "Search Results"
cmdExport.Tag = "Choose"
lstResult.ColumnWidths = ""
If Me.chkAutoBuildSQL = True Then Call sBuildSQL
End Select
ExitHere:
Exit Sub
ErrHandler:
If Err = 2448 Then Resume Next
Resume ExitHere
End Sub

 

[Steve Sanford]

Excel 3 is not a location, it is the type of file that the query results will
be saved as.

At the top of the Save As dialog box, you can pick the location where you
want the file to be saved; usually it is the "My Documents" folder.

Enter a name and decide where you want the file to be saved (remember the
location), then click the "Save" button. If you can't remember where you
saved it to (happens to me a lot - I am usually multi-tasking), do a search
on C: drive for the file name.

I saved findrecord2k.zip, extracted then ran the mdb. I imported a table,
selected fields and entered criteria. I clicked the Export button, picked a
file type (Excel 3), entered a name and selected a location. I clicked the
Save button and Ta-Da...I had an excel file. It opened no problem. So, if you
didn't modify the code, it works as advertised.


HTH