Solved

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
2
352 Views
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)
 xlWS.Select
 rowCnt = xlWS.UsedRange.Rows.Count
 xlWS.Columns.AutoFit
 xlWS.Range("J2:J" & rowCnt).NumberFormat = "#,##0.00"
Next
xlApp.ScreenUpdating = True
End Sub
0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 26

Expert Comment

by:Nick67
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.Activate
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
Next

'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
.EntireColumn.AutoFit
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)
oBook.Close
oApp.Visible = True
oApp.UserControl = True
oApp.Quit

'Close the Database and Recordset
rs.Close
db.Close





0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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.Workbooks.Open(filename)
     xlApp.Visible = True
     with xlApp
 
         For i = 1 To .workSheets.Count
          Set xlWS = .ActiveWorkbook.Worksheets(i)
              xlWS.Select
              rowCnt = xlWS.UsedRange.Rows.Count
              xlWS.Columns.AutoFit
              xlWS.Range("J2:J" & rowCnt).NumberFormat = "#,##0.00"
        Next
       .ScreenUpdating = True
       .activeWorkbook.save 
     end with

     xlApp.quit
     set xlApp=nothing
End Sub

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

729 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