VBA - and pulling data from user selected excel file

MYX

New Member
Joined
Mar 10, 2007
Messages
27
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 can.

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 to this).

So... My issue.
We (the fine, upright and proper pc folk :biggrin:) 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 VBA.

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.

Sorry, for 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)

workbook(PC).sheets("main").range("a1:a120")=workbook(MAC).sheets("main").range("a1:a120")
workbook(PC).sheets("main").range("e1:f120")=workbook(MAC).sheets("main").range("e1:f120")
etc.

Thanks folks
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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