Consider an Excel file that has an unknown number of columns and rows, but several of them (columns) need to be placed in a specific location on another worksheet.
So...
Sheet1 (the original file):
A B C D ? ?....
part#1 qty cost uom part#2 etc
value value value value value value
Sheet2 (the fixed file)
A B C D E F
part#2 part#1 qty cost (may not use).... uom
value value value value n/a value
I visualize this in either an Excel macro or an Access database(with a module). The user would be prompted to identify which columns in Sheet1 mapped to the correct column in Sheet2. I would prefer that the user be prompted using the column name like "part#" and the code will know where that entire column will be copied and pasted to in Sheet2.
The big addition that I also need (which I will gladly award an additional 500 points) is the ability to SAVE the mapping specific to the users file so that if a similar file comes along, you can pull up that "mapping" action again without having to go through the steps of identifying columns again through prompts.
I just need some help to get started as even I don't have all the information that I need, but this should be enough to get you going.
Thanks you fabulous programmers! :)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
~~~~~~
bruintje ,
it's possible that all or some of the columns will be needed in Sheet 2. The user will be prompted to identify all of the required files listed in Sheet2 that match to the undefined columns in Sheet1.
~~~~~~
shager ,
Using "offset" may not be the best approach as it is difficult to debug, it may be easier to treat each worksheet as a database table or just move one column from Sheet 1 to a specific column in Sheet2.
~~~~~~
The key is to do it in a way that can be saved for later use, whether it be a creation of a new macro or function that can be called later. In other words, each time you run the program it may be better to prompt the user if they want to use a previously saved "template" or create a new one.
I envision saving info like this to the registry and then call it from the registry and prompting user to accept or change.
I now don't know whether or not I am completely following what you're trying to do though without seeing further explaination from you.
bruintje seems to have a better handle on what you're trying to do.
I just thought of a couple of easier approaches using Access as the ultimate tool.
I use a form to import the excel file as a new table called: "file1", then the use pushes a button that activates a series of prompts or displays a screen allowing the user to identify these relationships (like a wizard) to "file2" (the fixed file) via column heading names. Once the users completes the identification portion, all of those variables are written to a NEW table that we'll use as the template with a user specified name.
Then the action of "mapping" from "file1" to "file2" uses the values inside the template table under the name they specified in order to loop through the records performing ADO updates. I think that this would be the most straight forward approach.
If you had an extra worksheet in your fixed file, you could save the mapping information. Suppose this worksheet was called Mapping. Each different mapping relationship would be described by a different row on the Mapping worksheet. For example, if you knew that column B on the fixed file received its information from column D in the source file, then cell B1 would contain a 4. With this method, you can map as many or few columns as required. If a different mapping relationship is required the next time, then you would use the same procedure in row 2. The Mapping worksheet can be hidden if you don't want the user tampering with it.
The task of processing a new file then would start with determining whether an existing mapping applies to the situation. If so, then use it. If not, create a new mapping and add another row.
I don't know whether it is programmatically obvious whether a mapping relationship will work. If not, then you might want to add a temporary worksheet to the fixed workbook. This temporary worksheet would display the results of the mapping. If you like it, then the columns are mapped over to the fixed file. If not, then the user would test the next mapping relationship. If none work, then the user would be prompted to specify which columns go where. This act would add a row to the mapping worksheet.
Business Accounts
Answer for Membership
by: bruintjePosted on 2003-06-06 at 06:38:20ID: 8666058
Hello Jaschlueter, do those columns need to be inserted or replacing an existing column?