PDA

View Full Version : Exporting Query from Access to Excel - Inconsistent Results



Enright
08-16-2016, 02:08 PM
Hello,

The code below is meant to export a query from Access and apply all necessary formatting to the file and saves the file to my desktop.

The functionality works, but there are some rough edges that I'm trying to polish off that have been bothering me:



Sometimes Excel will open up the file as intended, sometimes it just saves a copy to my desktop. I don't know what is causing this inconsistency.
When opening the the file for the first time on my desktop, I always get a 'This file is already open. Reopening will cause changes to be discarded etc...' I have to click "No" for the formatting to be preserved; otherwise, it will just be the raw unformatted query. How do I fix this?


Thanks!



Private Sub Command111_Click()
Dim myQueryName As String
Dim FileName As String
Dim myExportFileName As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim X11 As Excel.Workbooks
Dim XlSheet As Excel.Worksheet
Dim LR As Long
Dim LRR As Long
Dim LRRR As Long



FileName = Me.Vendor_Client & " Tax Refund Request - " & Me.Vendor_Name
myQueryName = "Refund Schedule Query"
myExportFileName = Environ("UserProfile") & "\Desktop\" & FileName & " Credit Schedule.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myQueryName, myExportFileName, True
'Excel Functions
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(myExportFileName)
Set XlSheet = XlBook.Worksheets(1)
Xl.Visible = True
XlBook.Windows(1).Visible = True
'Page Formatting and Footer
With XlBook.Worksheets(1).PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlLandscape
.CenterFooter = "&P of &N"
.LeftFooter = Me.Vendor_Client
.RightFooter = "&D"
End With
'Edits
XlSheet.Rows(1).EntireRow.Insert
XlSheet.Rows(1).EntireRow.Insert
XlSheet.Rows(1).EntireRow.Insert
XlSheet.Rows(1).EntireRow.Insert

With Range("A5: I5")
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With

XlSheet.Range("A1") = Me.Vendor_Client
XlSheet.Range("A2") = Me.Engagement
XlSheet.Range("A3") = Me.Vendor_Name & " Tax Refund Credit Schedule"
XlSheet.Rows(1).Font.Bold = True
XlSheet.Rows(2).Font.Bold = True
XlSheet.Rows(3).Font.Bold = True
XlSheet.Rows(4).Font.Bold = True
XlSheet.Rows(5).Font.Bold = True
XlSheet.Columns(12).EntireColumn.Delete
XlSheet.Columns(11).EntireColumn.Delete
XlSheet.Columns(10).EntireColumn.Delete
'freeze pane
XlSheet.Columns("B:I").AutoFit
'Sums
LR = Range("G" & Rows.Count).End(xlUp).Row
Range("G" & LR + 1).Formula = "=SUM(G6:G" & LR & ")"
LRR = Range("H" & Rows.Count).End(xlUp).Row
Range("H" & LR + 1).Formula = "=SUM(H6:H" & LR & ")"
LRRR = Range("k" & Rows.Count).End(xlUp).Row
Range("i" & LR + 1).Formula = "=SUM(i6:k" & LR & ")"
Range("G" & LR + 1, "i" & LR + 1).Font.Bold = True
With Range("G" & LR + 1, "i" & LR + 1)
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
'Sheet name
XlSheet.Name = "Refund Schedule"

End Sub

Jan Karel Pieterse
08-17-2016, 04:41 AM
Why not fetch the date from Excel directly using the Data, Get External data group?

Enright
08-17-2016, 05:54 AM
Why not fetch the date from Excel directly using the Data, Get External data group?

The Access database contains an interface that is used to manage a lot of client data. The Export to Excel feature is just a small part of the utility provided within the form. I'd really like to keep it within the Access to keep everything consolidated in one location.