[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 (https://msdn.microsoft.com/en-us/library/office/ff192065.aspx). 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.