Switch between active workbooks

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I am working with Excel for Mac. This has presented a number of challenges but I have successfully handled them. I am having a problem though and would love some input. I have a workbook that contains a pivot table. The pivot table is based on "Sheet1" of my workbook. The user will hit a button on the pivot table spreadsheet and the first operation is to select a file from their computer. I am using a function called ±MacScript to open the finder window and select the file. The file name (with full path) is stored in a variable called filepath. The file consists of a single sheet - Sheet1 and I select all and copy the cells. I then go back to the original workbook, select my worksheet with the source data for the pivot and I again select all and paste the new information over the old data. I have a named range called pivotsource that references the 9 columns that are required for the pivot table. What I would like to do now is go back to the original workbook and close it, do not save anything, just close it like nothing happened. I have tried to close it by using:

/* Workbooks(filepath).Activate
ActiveWindow.Close

*/

Since I had a large amount of data that is in the clipboard, I will also have to deal with the message coming up asking what I want to do with the clipboard. My bigger problem is that the activation of the filepath workbook doesn't work. How can I get back to the original workbook? I thought about the index of the workbooks but I don't know if the users will have other workbooks open at the same time. If they did, I could not reference it.

Thoughts anyone.

BTW I like the new look.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I've never used Excel on a mac, but with Windows the syntax for opening a file and activating a file is not the same.

To open, you use the full file path: Workbooks.Open("C:\someFolder\someFile.xlsx")
To activate, you use the file name: Workbooks("someFile.xlsx").Activate

I would assume that is the case here - so just adjust your code for activating the workbook to use the workbook's name, not the whole filepath.

Actually, you don't even need to activate a workbook to close it, so simply (using an error handler to avoid an error if the user has closed it already by hand):
Code:
On Error Resume Next
Workbooks("someFile.xlsx").Close SaveChanges:=False
On Error GoTo 0

ξ
 
Upvote 0

Forum statistics

Threads
1,211,435
Messages
6,101,842
Members
447,758
Latest member
jojo3strada

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
Back
Top