[VBA] Having problems with exporting file.

[echelon99]

Hi guys, very new to coding, and was basically thrown in the deep end. So apologies in advance for my extreme lack on knowledge, and would really appreciate specific code.

So this is how the export functionality is set up in the form, at the moment, anyway

Private Sub cmd_export_Click()

Me.frm_ORE_All.SetFocus
'DoCmd.GoToControl "OpERA ID"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
With xlapp
.Workbooks.Add
.ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
.Cells.Select
.Cells.EntireColumn.AutoFit
.Visible = True
.Range("a1").Select

End With

End Sub


AND basically it exports the data into a xls spreadsheet. I woud like it to go one step further, and save the spreadsheet automatically as soon as its generated. I tried adding the following code:



.Range("a1").Select
Dim xlWb As Workbook
Set xlWb = .Workbooks.Add
xlWb.SaveAs ("C:\Risk\Event Quarterly Trending" & "\" & Text1.Text & ".xls")

End With

End Sub



Doesn't seem to be working so far. Anyone give me specific ways out of this?

[InLaNoche]

I have never output data in that manner. Usually I output from a report or query. when doing so I use DoCmd.OutputTo. This way I can output to an existing xls file... Ok, you are thinking cart before the horse. here is an example of code that I have for doing something like this:

Private Sub Command4_Click()
    'requires Microsoft Office Object Library in References to use FileDialog
    
    Dim fd As FileDialog
    Dim sPath As String
    Dim objxls As Object
    Dim aFile As Object
    
    If MsgBox("Do you wish to instead export the Report to Excel", vbYesNo) = vbNo Then
        DoCmd.OpenReport "InReviewAndClosedSubmittals", acViewReport
    Else
        Set fd = Application.FileDialog(2)
        With fd
            .Title = "Location to Export EXCEL file"
            If .Show <> 0 Then
                sPath = .SelectedItems(1)
            Else
                sPath = "no.path"
            End If
        End With
        
        If sPath <> "no.path" Then
            If Right(sPath, 3) <> "xls" Then sPath = sPath & ".xls"
            DoCmd.OutputTo acOutputReport, "InReviewAndClosedSubmittals", "*.xls", sPath
            
            'open the file for view
            Set objxls = CreateObject("Excel.application")
            objxls.Visible = True
        End If
    End If
End Sub

there is some error checks in there to make sure that as the user goes to make the file, they name it right (and they don't cancel out). I also check to see if they typed in the extension xls or not, if not, I put it in (also if they put an invalid ext. it adds the .xls to the end and fixes that).

once the file is made, I dump the data to it with the OutputTo command. I am doing it from a report here, but you can pull from forms, queries and tables as well. Best to generate a query so you get the data the way you want it. This should also open the excel file at the end of the process, though for some reason it does not work on one computer here in my office (probably an issue with thier office install).

Hope this helps.