Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
356 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

783 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