SendKeys Help

[Guest]

In the program that I am working on, I copy and paste filtered data into a
new workbook multiple times. Each time that I complete this procedure, I set
the print preview to be set up a certain way. In order to be able to get out
of the print preview screen, I used SendKeys to click through it. When I run
just the sub for 1 instance of creating the new workbook, copying and pasting
the filtered data, etc., it works fine; but when I run the entire program and
it gets to the print preview screen, the SendKeys statement doesn't work. I
discovered that something causes the active screen to be inactive because if
I manually press Alt and C, nothing happens.

So, my question is, what can cause the screen to become inactive that would
cause SendKeys to not work? and what can I do to rectify this? thanks

This is the individual part that works fine by itself


Sub File1000()
Selection.AutoFilter Field:=2, Criteria1:="1000"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:G").Select
Selection.Columns.AutoFit
Columns("H:V").Select
Selection.ColumnWidth = 10
With ActiveSheet.PageSetup
.PrintTitleRows = "$4:$4"
.PrintTitleColumns = ""
End With
Range("A4").Select
Sheets(Array("Sheet2", "Sheet3")).Select
Sheets("Sheet3").Activate
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "1000"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 70
.PrintErrors = xlPrintErrorsDisplayed
End With
Application.SendKeys ("{%}c")
ActiveWindow.SelectedSheets.PrintPreview
Range("E2").Select
End Sub

 

[ducky]

In the program that I am working on, I copy and paste filtered data into a
new workbook multiple times. Each time that I complete this procedure, I set
the print preview to be set up a certain way. In order to be able to get out
of the print preview screen, I used SendKeys to click through it. When I run
just the sub for 1 instance of creating the new workbook, copying and pasting
the filtered data, etc., it works fine; but when I run the entire program and
it gets to the print preview screen, the SendKeys statement doesn't work. I
discovered that something causes the active screen to be inactive because if
I manually press Alt and C, nothing happens.

So, my question is, what can cause the screen to become inactive that would
cause SendKeys to not work? and what can I do to rectify this? thanks

SendKeys is one of the most horrible things in the world and should
only be used as an ABSOLUTE LAST resort. there is almost always an
alternative to using send keys. i hate them and i am now days closer
to death from time lost trying to get them to work.

Anyways- the problem you are encountering (i think) is that your window
is losing focus, or your computer can't keep up with the rate at which
they keys are being sent. Why is it that you care about seeing print
preview while your code is running? is anybody going to be watching
the macro execute? or are you just trying to use 'Page Setup' (and
getting there via print preview)?? if that is the case you can set up
attributes with VB without using sendkeys. the easiest way to
accomplish this would be to record a macro and actually completing the
steps manually. when you view the code excel creates for you, you will
see something like this, which can be modified as needed:

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

 

[NickHK]

As Ducky pointed out, if you are using code to manipulate and print the
worksheet, the user does not need to see the PrintPreview, so don't call it.
However, if the user does need to see PrintPreview, you do not need to close
it.
It should be 1 way or the other.

NickHK

 

[Guest]

Ok, thanks guys, that helped a lot.