Advertisement

12.01.2006 at 12:24PM PST, ID: 22079616
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.2

How to avoid the Microsoft Excel prompt "Do you want to save the changes you made to  *.XLS ? "

Asked by zimmer9 in Microsoft Excel Spreadsheet Software

Tags: ,

I am developing an application using Access VBA and SQL server. I create an Excel file which I modify via the following sub
procedure which I want to happend without user intervention.

When I call the following sub procedure as follows, do you know how I can avoid the prompt as follows:
I simply want the file to be modified and saved automatically.
------------------
Microsoft Excel

Do you want to save the changes you made to '487.XLS' ?
-------------------------------------------------------------------------
Sub Sort_add_Pagebreak(filename As String)

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xCell As Long
Dim Cell As Excel.Range
Dim Rng As Excel.Range
'Dim Rng As Range    'number of rows
Dim lngCOL As Long   'column number to use - user input
Dim lngROW As Long    'row count
Dim totalColumns As Long
Dim count As Integer
On Error GoTo EndMacro
 
'open excel template
Set xlApp = New Excel.Application
'xlApp.Visible = True
xlApp.Workbooks.Open (filename)

xlApp.ActiveSheet.ResetAllPageBreaks  'Clear existing page breaks


xlApp.ScreenUpdating = False
totalColumns = xlApp.ActiveSheet.UsedRange.Columns.count
count = 1
For count = totalColumns To 1 Step -1
Columns.Activate
Columns.WrapText = True
Columns.VerticalAlignment = xlVAlignTop
Next count

 Columns("A:A").Select
    Selection.ColumnWidth = 10
    Columns("B:B").Select
    Selection.ColumnWidth = 4
    Selection.EntireColumn.Hidden = True
    Columns("C:C").Select
    Selection.ColumnWidth = 12
    Columns("D:D").Select
    Selection.ColumnWidth = 10
    Columns("E:E").Select
    Selection.ColumnWidth = 4
    Selection.EntireColumn.Hidden = True
    Columns("F:F").Select
    Selection.ColumnWidth = 10
    Columns("G:G").Select
    Selection.ColumnWidth = 12
    Columns("H:H").Select
    Selection.ColumnWidth = 8
    Columns("I:I").Select
    Selection.ColumnWidth = 12
    Columns("J:J").Select
    Selection.ColumnWidth = 6
    Columns("K:K").Select
    Selection.ColumnWidth = 10
    Columns("L:L").Select
    Selection.ColumnWidth = 11
    Columns("M:M").Select
    Selection.ColumnWidth = 10
     Columns("N:N").Select
    Selection.ColumnWidth = 4
    Columns("O:O").Select
    Selection.ColumnWidth = 10
    Columns("P:P").Select
    Selection.ColumnWidth = 10
    Columns("Q:Q").Select
    Selection.ColumnWidth = 5
    Selection.EntireColumn.Hidden = True
    Columns("R:R").Select
    Selection.ColumnWidth = 14
    Columns("S:S").Select
    Selection.ColumnWidth = 10
    Columns("T:T").Select
     Selection.ColumnWidth = 10
    Columns("U:U").Select
    Selection.ColumnWidth = 4
    Columns("V:V").Select
    Selection.ColumnWidth = 10
    Columns("W:W").Select
    Selection.ColumnWidth = 11
    Columns("X:X").Select
    Selection.ColumnWidth = 6
    Range("A1").Select
    Selection.CurrentRegion.Select
      Cells.EntireRow.AutoFit
     
       Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
'Set pagebreaks in sorted data
        Range("A1").Select
       
lngCOL = 14 'correspond to column N (FAnumber)

Set Rng = xlApp.ActiveSheet.UsedRange.Rows

For lngROW = 3 To Rng.Rows.count  'start by comparing row 2 and 3
  If Cells(lngROW, lngCOL).Formula <> Cells(lngROW - 1, lngCOL).Formula Then
      ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(lngROW, lngCOL)
      xlApp.StatusBar = "Row: " + Format(lngROW)
  End If
Next lngROW
xlApp.StatusBar = "Done"
Set Rng = Nothing

'Page setup
    xlApp.StatusBar = "Preparing Pages"
    Range("A1").Select
    xlApp.ActiveSheet.PageSetup.PrintArea = ""
       With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = "$M:$M"
    End With
       With ActiveSheet.PageSetup
        .LeftHeader = "&D"
        .CenterHeader = "Undeliverable Accounts"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = xlApp.InchesToPoints(0.5)
        .RightMargin = xlApp.InchesToPoints(0.5)
        .TopMargin = xlApp.InchesToPoints(1)
        .BottomMargin = xlApp.InchesToPoints(1)
        .HeaderMargin = xlApp.InchesToPoints(0.5)
        .FooterMargin = xlApp.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .Order = xlOverThenDown
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
    End With
EndMacro:
        xlApp.ScreenUpdating = True
        xlApp.Workbooks.Close
        Set xlWS = Nothing
        Set xlWB = Nothing
        Set xlApp = Nothing
End Sub

Start Free Trial
[+][-]12.01.2006 at 12:27PM PST, ID: 18056464

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2006 at 12:32PM PST, ID: 18056496

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Microsoft Excel Spreadsheet Software
Tags: save, you
Sign Up Now!
Solution Provided By: p912s
Participating Experts: 3
Solution Grade: A
 
 
[+][-]12.01.2006 at 12:34PM PST, ID: 18056507

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2006 at 12:36PM PST, ID: 18056520

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2006 at 12:37PM PST, ID: 18056537

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]12.01.2006 at 12:44PM PST, ID: 18056571

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.01.2006 at 12:47PM PST, ID: 18056591

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]12.02.2006 at 08:04AM PST, ID: 18059978

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32