Print Preview (2000 vs 2013)

[DannyDont]

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.

 

[mole999]

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

 

[DannyDont]

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"?

 

[mole999]

have a read over > Save Active Sheet As New Workbook, Formats & Values Only

 

[DannyDont]

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.