Do you know how I can avoid the message prompt when I hit the close button once an Excel file is dipllayed on the screen?

Posted on 2011-02-25
Last Modified: 2012-05-11
I am developing an Access 2003 application that writes out a query to an Excel report.

Do you know how I can avoid the message prompt, when I hit the close button, once an Excel file is dipllayed on the screen?

I just want the Excel file to be created automatically with no message prompt.

The message prompt states the following:

Do you want to save then changes you made to 'OpenItemsDetails_Monthly_BY_MGMT_TAB02251854545.xls

Yes     No       Cancel

My VBA code is as follows:

If isFileExist(exportedfile) Then StartDoc1 exportedfile

Private Sub StartDoc1(filename)

 Dim xlApp As Object
 Dim xlWB As Excel.Workbook
 Dim xlWS As Excel.Worksheet
 Dim i As Integer, rowCnt As Long
 Set xlApp = New Excel.Application
 xlApp.Visible = True
 Set xlWB = xlApp.Workbooks.Open(filename)
 Set xlWS = xlWB.Worksheets(1)
For i = 1 To xlApp.Sheets.Count
 Set xlWS = xlApp.ActiveWorkbook.Worksheets(i)
 rowCnt = xlWS.UsedRange.Rows.Count
 xlWS.Range("J2:J" & rowCnt).NumberFormat = "#,##0.00"
xlApp.ScreenUpdating = True
End Sub
Question by:zimmer9
LVL 26

Expert Comment

ID: 34984987
Hey zimmer9

I'm not sure where your data is going into the spreadsheet from your code --
and it looks like you are opening an existing workbook, not creating a new one--
But that's neither here nor there.  I don't see most of the code that you'd need.

Private Sub ExportToExcel(myFullFilePath as string)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String

SQL = "Select Something from SomeTable;"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
'add a sheet
Set oSheet = oBook.Worksheets(1)
oSheet.Name = "Some Sheet Name"
oSheet.PageSetup.Orientation = xlLandscape 'optional
oApp.ActiveWindow.View = xlPageBreakPreview 'optional
oApp.ActiveWindow.Zoom = 100  'optional

'Add the field names in row 1
Dim I As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.count
For I = 1 To iNumCols
oSheet.Cells(1, I).Value = rs.Fields(I - 1).Name

'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs

'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
End With

***Here's the stuff I expected to see in your code but don't ***
Dim myfilename As String
myfilename = myFullFilePath
oSheet.SaveAs (myfilename)
oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset

LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 34985110
test this code
Private Sub StartDoc1(filename)

 Dim xlApp As Object
 Dim xlWS As Object
 Dim i As Integer, rowCnt As Long
 Set xlApp = createobject("excel.application")

     xlApp.Visible = True
     with xlApp
         For i = 1 To .workSheets.Count
          Set xlWS = .ActiveWorkbook.Worksheets(i)
              rowCnt = xlWS.UsedRange.Rows.Count
              xlWS.Range("J2:J" & rowCnt).NumberFormat = "#,##0.00"
       .ScreenUpdating = True 
     end with

     set xlApp=nothing
End Sub

Open in new window


Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now