Excel Character Fix
My intent here is to modify the existing 5 characters on the worksheet such as (“, ‘, —,”, ’) into a desired format (commas here are not part of the issue and are just for separation). The reason behind that is when the worksheet is loaded to Oracle tables, they are not recognized as they are and Oracle shows them with question mark as it doesn’t know how to interpret them. I have been manually replacing text with question marks and converting them to right characters like (",',-,",') that Oracle recognizes. On this worksheet located on the attached file I came up with a macro (basically a compilation of recorded macros) that replaces wrong characters with the right ones as listed. The tab "TestContents" has this problem and it is the original (unfixed) data . On the "FixedContents" tab I copy from the “TestContents” tab to see the impact of the macro. When I click on the blue box which is assigned to “SpecialCharacterFix” macro on the "FixedContents" tab (with “Fix the special character issues” text) the conversion is completed. This macro is doing what it needs to be doing and I have no problem with that. My question is how to create an Add-On doing what this macro does and obviously it needs to be more elaborate for the VBA part than what I have here like having variables and inserting the right characters for the wrong ones that are defined in the code. My macro makes the replacements by looking at the range Y1:Y5 to get the desired format on "FixedContents" tab then go back to the range on the same tab to complete the planned replacements. “Reference” tab shows the desired format for the characters under the title “Good” and undesired ones under the title “Bad”. On the "FixedContents" tab I have the same charater list in E1:E5 and F1:F5 that I use this range as my indicator after running macro to see the results immediately for the conversion.
SpecialCharacterFix.xlsm
SpecialCharacterFix.xlsm
Were you able to manage with my comment or you need further help ?
gowflow
gowflow
ASKER
gowflow - Thank you for your posting and also checking with me later.
I followed your steps and created the Addin file ok with the xla extension. But the problem here is that when I try to make it work on a file to convert the characters I wasn't as succesful. This is what I am trying to accomplish with this Addin. It should open the "Content" tab of the file that needs to be fixed then apply the changes to the range under the "HeaderText" and "BodyText" fields. I am trying to convert the characters (“, ‘, —,”, ’) to (",',-,",') in those ranges. As I mentioned on my previous posting I use a recorded macro hence it is not as elaborate. I am trying to have a code where variables are created and replacement is done all in one code block.
Thank you
I followed your steps and created the Addin file ok with the xla extension. But the problem here is that when I try to make it work on a file to convert the characters I wasn't as succesful. This is what I am trying to accomplish with this Addin. It should open the "Content" tab of the file that needs to be fixed then apply the changes to the range under the "HeaderText" and "BodyText" fields. I am trying to convert the characters (“, ‘, —,”, ’) to (",',-,",') in those ranges. As I mentioned on my previous posting I use a recorded macro hence it is not as elaborate. I am trying to have a code where variables are created and replacement is done all in one code block.
Thank you
ASKER
gowflow - following up on my previous posting, just attached the xla file here, it is basically the same file saved as Addin but can't use it the way I intended. First thing, when I drag it onto the file I need to work on I expect to see it under the Add-Inns tab on top right corner right under the File where Ican see my other Add-Inns created by another person. But it doesn't show up there.
SpecialCharacterFixAddin1.xla
SpecialCharacterFixAddin1.xla
well don't know what u did but I took your file that you posted in the question and saved it as addin with extention *.xlam then once this is done I opened a new project to which I added the addin to do this do the following:
1) Open the project
2) Click on the Office button then Excel Options
3) CLick on Addin
4) You should see your file at the buttom of the list with its name. down in the window you have Manage Excel Addin press on Go it will open a window and you should find your addin there just click on it press ok and here you go.
I believe as you saved your addin with *.xla it did not show there choose the other addin *.xlam
Let me know.
gowflow
1) Open the project
2) Click on the Office button then Excel Options
3) CLick on Addin
4) You should see your file at the buttom of the list with its name. down in the window you have Manage Excel Addin press on Go it will open a window and you should find your addin there just click on it press ok and here you go.
I believe as you saved your addin with *.xla it did not show there choose the other addin *.xlam
Let me know.
gowflow
ASKER
I saved it again this time with .xlam extension and it does show under the Add-Ins group under Developer tab, also shows under Options>Add-Ins by its name (SpecialCharacterFix.xlam)
The problem that I am having is this:
After making this add-in file available to Excel I tried to use it to get it done what it needs to do and this is where I fail. I am sending you another sample file where I need to run the code to fix the special characters in the range. Could you please try to use this add-in on this sample file to achieve what I can do running the macro on the file fixing the special characters? Or is the way my so called add-in is not suitable to be used like that and needs improvements or modifications?
After making the add-in available to any Excel file I was expecting to see it under the Add-Ins tab on the ribbon. Once this tab is opened add-ins get displayed on the top left corner of the bar right below the ribbon menu options. I have two other add-ins in that location visible and ready to be used but my add-in does not show up there.
Sample.xlsx
The problem that I am having is this:
After making this add-in file available to Excel I tried to use it to get it done what it needs to do and this is where I fail. I am sending you another sample file where I need to run the code to fix the special characters in the range. Could you please try to use this add-in on this sample file to achieve what I can do running the macro on the file fixing the special characters? Or is the way my so called add-in is not suitable to be used like that and needs improvements or modifications?
After making the add-in available to any Excel file I was expecting to see it under the Add-Ins tab on the ribbon. Once this tab is opened add-ins get displayed on the top left corner of the bar right below the ribbon menu options. I have two other add-ins in that location visible and ready to be used but my add-in does not show up there.
Sample.xlsx
Well lets take it one step at a time.
1
In your first file you have 3 modules and 4 sheets I did not look to see how this work altogether can you tell me what are the module or sheets that are not mandatory to be or that we can delete to have at least the add-in concise and clearcut.
2
I need to know after you do the cleanup how you proceed like what you invoke on what sheet and what you get like the functioning. When all this is clear then I can comment and see how we can incorporate all this into a menu that you will be able to use on any workbook
gowflow
1
In your first file you have 3 modules and 4 sheets I did not look to see how this work altogether can you tell me what are the module or sheets that are not mandatory to be or that we can delete to have at least the add-in concise and clearcut.
2
I need to know after you do the cleanup how you proceed like what you invoke on what sheet and what you get like the functioning. When all this is clear then I can comment and see how we can incorporate all this into a menu that you will be able to use on any workbook
gowflow
ASKER
Attached are three file names and their descriptions:
A. Sample1.xlsx - This file holds the sample data to be fixed by the macro to illustrate the
way macro should be working.
B. SpecialCharacterFix.xlam - This is the Add-In file that I created from the file with macros and made available to Excel files as described on the previous steps.
C. SpecialCharacterFix.xlsm - This file holds the modules and macros that I have been succesfully using to fix data ranges with special character issues.
On the file C. there are two modules (1 and 2). Module 1 holds 5 macros and their names are listed below and module 2 makes macro calls to run them all on the range to fix the special character issues.
Macro Names are,
Module1:
DoubleQuoteRight
DoubleQuoteLeft
SingleQuote1
SingleQuote2
Dash
Module2:
SpecialCharacterFix (calls above macros)
File C. tabs are lsited below:
Reference - Shows the good/bad charaters
TestContents - Holds the range with problems
FixedContents - Copy the range from the TestContents and run the macro to se the impact
How do I use above macros to resolve my issue with special characters:
1. Open the file C.
2. Open the file A.
3. Copy from the sample file's range with issues into the file C. FixedContents tab
4. Click on the blue box sauying "Fix the special character issues" on it. It is assigned to the macro making macro calls.
5. After macro run is complete I copy from the range on the C. which is fixed back to the sample file's corresponding range or whatever the file that I am working on. At this point all the special character issues are fixed so I can proceed loading from this Excel file to Oracle table w/o any problems.
Below are what the problem charaters are and how they should be:
Bad Characters - (“, ‘, —,”, ’)
Good Characters - (",',-,",')
My Questions are:
1. Is it possible to use the file C. as an Ad-In, I had doubts about that as its macros are based on recorded macro and may not be structered good enough to be used as an Add-In.
2. If one is a correct concern then is it possible for EE to make my macros more sophisticated so they can be utilized as an Add-In so anyone can use this without having a need of using the file C. holding my modules.
Please let me know if anything is unclear or missing or misleading.
Thank you for your help
Sample1.xlsx
SpecialCharacterFix.xlam
SpecialCharacterFix.xlsm
A. Sample1.xlsx - This file holds the sample data to be fixed by the macro to illustrate the
way macro should be working.
B. SpecialCharacterFix.xlam - This is the Add-In file that I created from the file with macros and made available to Excel files as described on the previous steps.
C. SpecialCharacterFix.xlsm - This file holds the modules and macros that I have been succesfully using to fix data ranges with special character issues.
On the file C. there are two modules (1 and 2). Module 1 holds 5 macros and their names are listed below and module 2 makes macro calls to run them all on the range to fix the special character issues.
Macro Names are,
Module1:
DoubleQuoteRight
DoubleQuoteLeft
SingleQuote1
SingleQuote2
Dash
Module2:
SpecialCharacterFix (calls above macros)
File C. tabs are lsited below:
Reference - Shows the good/bad charaters
TestContents - Holds the range with problems
FixedContents - Copy the range from the TestContents and run the macro to se the impact
How do I use above macros to resolve my issue with special characters:
1. Open the file C.
2. Open the file A.
3. Copy from the sample file's range with issues into the file C. FixedContents tab
4. Click on the blue box sauying "Fix the special character issues" on it. It is assigned to the macro making macro calls.
5. After macro run is complete I copy from the range on the C. which is fixed back to the sample file's corresponding range or whatever the file that I am working on. At this point all the special character issues are fixed so I can proceed loading from this Excel file to Oracle table w/o any problems.
Below are what the problem charaters are and how they should be:
Bad Characters - (“, ‘, —,”, ’)
Good Characters - (",',-,",')
My Questions are:
1. Is it possible to use the file C. as an Ad-In, I had doubts about that as its macros are based on recorded macro and may not be structered good enough to be used as an Add-In.
2. If one is a correct concern then is it possible for EE to make my macros more sophisticated so they can be utilized as an Add-In so anyone can use this without having a need of using the file C. holding my modules.
Please let me know if anything is unclear or missing or misleading.
Thank you for your help
Sample1.xlsx
SpecialCharacterFix.xlam
SpecialCharacterFix.xlsm
ok tks your very detailed explanation
I have following questions:
1) Is your TextContent file or sheet always same format ? like it has data in columns
A and B only and these are the columns you always want to fix as per your table of bad/good characters ???
2) Can we do it in a way that we cut all this back and forth between several workbooks and sheets and fix the data in the same sheet that you submit ?? will this be a problem ??
The way I see it you have a macro (will decide where and how) that when you run it will 'convert' the active sheet into a 'workable' data for Oracle for you. Would this be ok ??
gowflow
I have following questions:
1) Is your TextContent file or sheet always same format ? like it has data in columns
A and B only and these are the columns you always want to fix as per your table of bad/good characters ???
2) Can we do it in a way that we cut all this back and forth between several workbooks and sheets and fix the data in the same sheet that you submit ?? will this be a problem ??
The way I see it you have a macro (will decide where and how) that when you run it will 'convert' the active sheet into a 'workable' data for Oracle for you. Would this be ok ??
gowflow
ASKER
A1. In actual file normally data is in V and W columns I put data range in A nad B columns as it was the sample file. But the intent is to fix these two columns. I might use the same macro if special character issue shows up on a different tab too by copying between the problem file and the macro file back and forth. But for the most part it is the way depicted on the sample file.
A2. Yes certainly, please feel free to modify my macro and cut all this back and forth action and fix the issue on the same sheet by all means. If you think coming up with entirely different process don't hesitate to do so. Like I said my macro is not elaborately done and I am open to any improvements.
For your last comment - Yes that would be ok.
A2. Yes certainly, please feel free to modify my macro and cut all this back and forth action and fix the issue on the same sheet by all means. If you think coming up with entirely different process don't hesitate to do so. Like I said my macro is not elaborately done and I am open to any improvements.
For your last comment - Yes that would be ok.
Sorry for the delay in replying, I did not forget you. I was busy on other urgent stuff. I will attend this very soon.
The way I see it I will build a macro in a workbook and you try it and if all ok will see how to integrate this so it is available to all workbooks ok like this ?
I would see this in 2 parts.
First part which is this question is to provide you with my solution to the problem in 1 workbook if this is fine then we move to
Second part you ask a new question related to this one where we work on integrating this workbook as global.
Pls confirm your agreement and upon that I will start.
gowflow
The way I see it I will build a macro in a workbook and you try it and if all ok will see how to integrate this so it is available to all workbooks ok like this ?
I would see this in 2 parts.
First part which is this question is to provide you with my solution to the problem in 1 workbook if this is fine then we move to
Second part you ask a new question related to this one where we work on integrating this workbook as global.
Pls confirm your agreement and upon that I will start.
gowflow
ok here is hoe it works:
1) Load this file and run it after enabling macros. Once it run, it will tell you Delete Menu press ok.
2) Now it has created a new Menu go to you Add-in Tab and you will see a new Menu Called Oracle Menu click on it and you will see a sub Menu Called Special characters Fix
3) If you press on this button it will Fix whatever sheet you have that is the active sheet. Now this workbook has only one sheet so it will fix that sheet.
4) Press on that menu and answer the question and check the results.
5) While still in this menu you can also request this menu again and see the results.
This is for this part.
Let me know your comments.
gowflow
SpecialCharacterFixV01.xlsm
1) Load this file and run it after enabling macros. Once it run, it will tell you Delete Menu press ok.
2) Now it has created a new Menu go to you Add-in Tab and you will see a new Menu Called Oracle Menu click on it and you will see a sub Menu Called Special characters Fix
3) If you press on this button it will Fix whatever sheet you have that is the active sheet. Now this workbook has only one sheet so it will fix that sheet.
4) Press on that menu and answer the question and check the results.
5) While still in this menu you can also request this menu again and see the results.
This is for this part.
Let me know your comments.
gowflow
SpecialCharacterFixV01.xlsm
ASKER
For the similar reasons I am responding to you back with some delay so no worries. Responding to your previous posting yes the approach that you described is just fine and that was what I actually envisioned anyway. For the recent posting, will let you know on the status when I have more time to use your solution on the attached file. Its been real hectic here. Thank you for keeping me in the loop.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for your help this works awesome just the way I wanted.
If you need more help pls do not hesitate to post a link for a new question here I will be glad to assist.
Rgds/gowflow
Rgds/gowflow
btw if you need an integration so that you don't open this workbook let me know.
gowflow
gowflow
ASKER
goflow - Thank you for your followup even after the closed status of my question.
I integrated your solution now that when I open any Excel file, under Add-Ins tab on the ribbon I can see that Oracle Menu>Special Character Fix option. So this works pretty good for me now. But if I do comeup with a question related to this posting or any other posting I will try to let you know directly. Not sure if EE system alows this but will try.
Thank you again
I integrated your solution now that when I open any Excel file, under Add-Ins tab on the ribbon I can see that Oracle Menu>Special Character Fix option. So this works pretty good for me now. But if I do comeup with a question related to this posting or any other posting I will try to let you know directly. Not sure if EE system alows this but will try.
Thank you again
Ok great. I will keep this question monitored and if you have any other question related or not that you may need help with pls post a link here and will be glad to assist you.
Regards/gowflow
Regards/gowflow
I guess you mean Addin not Addon just to start with, and if this is the case, then you need to do the following:
To put your code in an addin (which can easily be distributed to other users too), create a new workbook. Put all the modules/code/forms/etc into that workbook. I usually delete all but the first worksheet, as they're not necessary in an addin (unless your code uses them) and it just makes the file unnecessarily larger.
Then, from excel, go to File / Save As. The last option of 'Save as type" is "Microsoft Excel Add-In (.xla)". Save it as an .xla file, then you might want to save it as an .xls as well just so you have a backup copy. The .xla file should save (by default) in your AddIns folder, makes it easier later. If you decide on a different location, just remember where you saved it. Close the file, then go to Tools / Add-Ins (you need an active workbook, so if nothing is open, just open up a new one). Click Browse, then find the .xla file you just saved. When you go in VBA you'll notice the VBAProject for the .xla is there. You may want to rename the (Name) property of the adding from VBAProject to something distinguishable, but thats not necessary.
You can still make changes to the add-in within VBA, but I want to warn you that excel will not say "filename.xla has changed, do you want to save" when you close excel. So any changes you do make, make sure you Save within VBA when your add-in name is in the title bar of VBA (when you're actively on an object within the addin).
gowflow