Ok, that was a little bit of a mouth full.
Brief history so you understand the application I am trying to update.
When I started working at my current job, I saw a lot of frustration because we had a group of people who had to fill out an
excel document, send it to us, we would then enter the data into another program. REALLY stupid mistakes were being made on
the incoming sheets like bad naming conventions spaces between words (this is bad in our world), and we deal a lot with time
tracking and we would sometimes get times that ended before they began. Then there is the human element where we would need
to read that data and enter it into our program for it to process all this, so secondary mistakes were made in translation
and/or mis keying. So, about a year and a half ago I built an excel application to try to reduce mistakes. It looks for
common mistakes so the user can correct them prior to sending it to us. It adds underscores where there are currently spaces
or dashes, exports to csv in several flavors based upon what we need. These csv files can be imported directly into our
work program so translation is no longer an issue (unless it was sent as an error). This has no allowed us to focus on our
work and saves time because there are far less mistakes, and we do not have the added time involved in inputting all the
data. I surprised myself with how well it worked, and how much I learned in the process and am still learning as much as I
Sub MonkeyWrench ()
Many users are now using macs. The the app. completely blows in mac land. After a lot of trying, I gave up on the idea of
building a complete version for mac. So, I gave them a front end version using only the standard excel formulas to make data
input page still work. All the little warnings if the data was out of spec or named incorrectly, or not complete still work
due to the basic excel functionality. As far as the structure of the main page, everything is identical to the pc version.
Same cells, same formulas, just no backend.
When the PC version starts, the user is presented with a form which
demands certain info before they are allowed to continue (Their name, project number, project name). This populates various
cells in the main page and also names their workbook with the proper naming convention (Project Num and Project Name are key
So... My issue.
We (the fine, upright and proper pc folk ) have to process the information sent over from users who are using macs. It is
still easier as it is now just copying and pasting into the appropriate locations (which has led to mistakes). But, we would
like to use the abilities in the pc version as it is a much more efficient environment throughout. So, I am mid process of
updating my excel program. The key issue addressed in this post, getting the data out of the mac version and into ours via
When they email over their file, Lord only knows what it will be named, so I can not program a name to look
for. I am trying to use getopenfilename and this part is working. I can get the user selected filename. I assign it to a
string. At the same time I also grab the path as a string too so it will eventually automatically name the PC version file. I
have found that I do need to open the mac version (I am going to just to refer to it as mac from here). I do not have a
problem with this as I can just turn off screen updating. Where I am getting thrown under the bus is that I can not figure
out how to swap the focus between PC and MAC. I have tried workbooks ("MAC").activate, with and without "s... no good. I
tried to pull it into new strings as
MacName as string
MacName = workbook.name
but this did not work. I spent hours trying everything I could think of to make it go. But I am beyond me. I have read lots
of stuff on this and other sites as well as the VBA helps but still can not seem to make it work.
being so long winded, but I felt the application was important. Perhaps not.
So, any ideas?
BTW, once I can call the workbooks back and forth at will,
to pull the data can I use something similar to the following?
(remember the cell alignment is identical)