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(filenam
e 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.ResetAll
PageBreaks
'Clear existing page breaks
xlApp.ScreenUpdating = False
totalColumns = xlApp.ActiveSheet.UsedRang
e.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.Hid
den = True
Columns("C:C").Select
Selection.ColumnWidth = 12
Columns("D:D").Select
Selection.ColumnWidth = 10
Columns("E:E").Select
Selection.ColumnWidth = 4
Selection.EntireColumn.Hid
den = 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.Hid
den = 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.Se
lect
Cells.EntireRow.AutoFit
Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
, _
DataOption1:=xlSortTextAsN
umbers
'Set pagebreaks in sorted data
Range("A1").Select
lngCOL = 14 'correspond to column N (FAnumber)
Set Rng = xlApp.ActiveSheet.UsedRang
e.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.SelectedSheet
s.HPageBre
aks.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.PageSetu
p.PrintAre
a = ""
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