Using Excel VBA to modify .zoom property in another workbook

Question

I am writing a complex VBA app that imports data from one workbook, transforming the data as it imports. As part of this import process, a calculation is made that picks up the .text value of a certain cell (and this cannot be changed to .value), however this means if the source workbook is zoomed out, this imports ######## values, as that is what is displayed.

I am trying to modify the .zoom setting in the activesheet of the source workbook to be 100% zoom. An example of the code used is below.

Dim SourcePath as String    
Dim SourceWorkbook as Object

FilePath = Application.GetOpenFilename 'Opens dialogue for user to select source
    If FilePath <> "" Then 'Checks that the filepath is completed
       SourcePath = FilePath 'Stores the filepath for source
    End If

Set SourceWorkbook = Workbooks.Open(SourcePath)

SourceWorkbook.ActiveWorksheet.Zoom = 100

I am getting a run-time error 91: Object variable or With block variable not set error, so I am assuming the logic here is poor.

Can anybody suggest a way of making this work? Thanks in anticipation.

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

There's no zoom property on worksheet, i think you need to change zoom property of a window!

Setting all Excel sheets at a defined zoom level

edited May 23 '17 at 11:45

Community

1
1

answered Dec 16 '16 at 12:42

CommonSense

4,232
2
14
38

score 1 · Accepted Answer · answered Dec 16 '16 at 12:56

1

You can use the following code

    Sheets("Sheet Name").Select
    ActiveWindow.Zoom = 30 ' Zoom Set to 30%

answered Dec 16 '16 at 12:56

RAJA THEVAR

421
1
5
19

Perfect, thank you! I shall try the following logic: Workbooks(SourceWorkbook).Worksheets(SourceSheet).Select ActiveWindow.Zoom = 100 Workbooks(DestinationWorkbook).Worksheets(SourceSheet).Select Really appreciate this, thank you! – D10N-CB3 Dec 16 '16 at 13:27

Using Excel VBA to modify .zoom property in another workbook

2 Answers2