Print Preview (2000 vs 2013)

DannyDont

New Member
Joined
Mar 7, 2014
Messages
24
I am creating a new Workbook using VBA from another Excel Workbook. I then want to perform a PrintPreviewof the new Workbook. However, the whole thing hangs. I believe this is because the PrintPreview is executed before Excel has completed creating the new Workbook. I have looked and looked and tried different things to get this to work. How can I do this. I have included several different things that I tried. See below.

NOTE: The code was originally written in Excel 2000 and works just fine!

I found the following code on the Internet somewhere:

Sub UTY_Manifest_Wait_For_Open()
Dim wb As Workbook

Set wb = Application.Workbooks.Open(fullname of just created file)

Do Until wb.ReadOnly = False
wb.Close

Application.Wait Now + TimeValue("00:00:01")

Set wb = Application.Workbooks.Open(fullname of just created file)
Loop

'Then the code that needs that Workbook open here!
'Or Call That other macro here!
ActiveWorkbook.PrintPreview (True)
End Sub

This did not work.

This is what I tried next ... which I do not like ... I don't like using a fixed wait time even though I might be able to tweak it to get it as short as possible.

Application.Wait (Now + TimeValue("0:00:04"))
ActiveWorkbook.PrintPreview (True)

This didn't work either.

I have tried all kinds of other things. Being able to do this PrintPreview has evaded me for over a year. The original code runs just fine in Excel 2000 (xls). However when running the XLS version in compatibility mode or running an XLSM version of the code in Excel 2016 it does NOT!

What will I do????? I appreciate any suggestions.
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
2000 and and 2013 are fundamentally different, later varients moved wholesale to xml, that said I would try "DoEvents" instead of a fixed time

recording the action it gives

ActiveWindow.SelectedSheets.PrintPreview
 
Last edited:

DannyDont

New Member
Joined
Mar 7, 2014
Messages
24
2000 and and 2013 are fundamentally different, later varients moved wholesale to xml, that said I would try "DoEvents" instead of a fixed time

recording the action it gives

ActiveWindow.SelectedSheets.PrintPreview

This did not do it when plugged into the program.

By the way, the code works fine if I step through the VBA code or if I have a Stop or debugging "break" in the code. Again, I believe this allows Excel time to create the new Workbook that I want to PrintPreview. I still "think" it is a timing issue more that a specific instruction but I don't really know.

Interesting the last time I ran, the Excel program locked up. But rather than kill it with Task Manager I need to shut my computer off to leave for other location. When I did this I found the requested Print Preview showing as one of the applications that was stopping Windows for closing. It must be hidden in the background? So maybe it is there and I just don't know how to get to it. This might also point to a timing issue.

Thanks for your initial thought. I really appreciate your feedback.

I am building the new workbook as a worksheet in the original workbook and then copying it to the new Workbook. I am doing this because I 'believe" it to be faster than creating a new blank workbook and then adding the information there rather than on a new worksheet in the original workbook. Perhaps you have thoughts about that "belief"?
 

DannyDont

New Member
Joined
Mar 7, 2014
Messages
24
It had nothing to do with the PrintPreview as it turns out. I use:

Application.ScreenUpdating = False 'Turn off any Screen activity
Application.ScreenUpdating = True 'Turn on any Screen activity

I set False at the beginning so that the updates to the new worksheet to not show on the screen. But I did not set the True until the process was complete. Then I determined that it was a good idea to turn True on before the PrintPreview. That didn't work so I put it just before the With wb command. That didn't work. Then I finally determined that it had to be outside the following code:

Dim wb As Workbook
Sheets(Control_Tab_Name).Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
With wb
.SaveAs ThisWorkbook.Path & "" & _
Left(CSVFile_File_Name, Len(CSVFile_File_Name) - 4) & _
".xlsx"
Application.DisplayAlerts = True
End With

So now the code is:

Application.ScreenUpdating = True 'Turn on any Screen activity
Dim wb As Workbook
Sheets(Control_Tab_Name).Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
With wb
.SaveAs ThisWorkbook.Path & "" & _
Left(CSVFile_File_Name, Len(CSVFile_File_Name) - 4) & _
".xlsx"
Application.DisplayAlerts = True
End With

WOW how convoluted was this to figure out!!!!! Yet the original code worked just fine in Excel 2000. How can one ever learn of all the internal changes that take place from one MS release to the next??? UGH!

I believe the problem is solved. More testing required but early results appear to be working fine.

Thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,792
Messages
5,655,332
Members
418,190
Latest member
Timex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top