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
345 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
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 119

Accepted Solution

by:
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.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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Explanation of Access VBA code 2 35
sort Time by AM and PM in query 2 18
Importing and Dropping Table in Access 11 23
I need to be able to get MAX(date)-1 from table. 4 25
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

20 Experts available now in Live!

Get 1:1 Help Now