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
348 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 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