SOLVED Editing A Macro

[9januerry9]

No matter what file I retrieve in Excel, it opens 3 files -- (1) the file I requested, (2) Personal and (3) Personal (Autosaved). What do I have to do to simply have the file that I requested appear and not the other two?

 

[clifford_cooley]

The (Autosave) files only opens if Excel in improperly closed. You should have the option to remove them after loading Excel again. If not you can manually delete the autosave temp file, found in the same directory as the Personal.xls file.

The "Personal.xls" file is usually not that big of deal. That is if you leave it hidden, where you normally don't see it.

 

[9januerry9]

Yes. I don't know if this makes any sense but the PERSONAL file has to be there. This morning I closed that file (didn't remove it from its directory because I can't find it) and then when I went through my exporting routine from Linked In and tried to invoke the Macro,the Macro code wasn't there. I clicked on DEVELOPER and examined what was in Macros and Visual Basic. Nothing in either. I panicked. I was about to reach out to you again through the Forum. But then I thought that I would simply try to open a new Excel file. I did. And its 3 friends PERSONAL, PERSONAL (Autosaved) and BARBARAS PAYROLL opened as well. I don't know how the last file got into the XLSTART Routine, but its there. How to get rid of it is a subject for another day. In any event, I clicked on Macros and there was the code. I then did the Export from Linked In and the Macro performed its magic.

 

[clifford_cooley]

OK I think I noticed something. I was slightly confused as to why I didn't currently have the personal.xls file. So I decided to find out why, and what it would take to activate it so easily.

When you recorded your macro you must have chosen to "Store macro in: Personal Macro Workbook". Now that you understand a bit about the Personal.xls file, you may understand why that is an option, and that the other options may be what you are actually needing.

If you record another macro using "Store macro in: This Workbook", you can copy your original code to the new macro. Doing this will place new macros in the appropriate location for moving, so you won't have to guess. Once this is done you shouldn't need the Personal.xls file, because then you will have it stored in the file needing the code.

 

[clifford_cooley]

I need you to check and see if your startup location is the same as your store location. There is a way to set Excel to open all files in a specific folder. We need to check to see if this folder is one of your store folders.

I'm using Excel 2010 so settings may be a bit different than yours. There is not a folder selected in my settings for startup.

File >> Options >> Advanced [tab] >> At startup, open files in:​

 

[9januerry9]

Under the Advanced Tab, I found nothing that matched what you have in Excel 2010. I Googled what you asked about and the best thing I could find is under Options, Trust Center. I am attaching a snip of what's there.

I really appreciate you going the extra mile here. I never could have figured out what is causing three files to open every time I open an Excel file.

 

[clifford_cooley]

OK that is the trust center settings. My trust center settings look similar.

I just confirmed that 2013 navigation for this setting is identical to 2010

From an open workbook >> File >> Options >> Advanced [tab]
And then scroll down to "At startup, open files in:".

 

[9januerry9]

OK! I finally found it. The box next to it is blank.

 

[clifford_cooley]

In that case Excel shouldn't be loading anything automatically at startup. I don't have an answer for that question.

 

[wegmand]

Wait a minute. Why doesn't it hold that setting. After I close the file and then reopen it, it's back to Manual.

I use Excel 2013. It has a setting under the FORMULAS tab for Calculation options. See image:

 

[9januerry9]

Thanks for that WEGMAND. I am now taking advantage of those settings.

New discovery for clifford_cooley! Answer Desk at Microsoft helped to resolve why three files were opening simultaneously. I'm not sure where this is, but there is a folder called XLSTART. The technician went there and removed those three files from that folder. Voila! Now Excel opens with a generic Book1.

Before he did this we saved the Macro to Notepad. Then copied the Macro to a vanilla Book1 and saved it.

Now when I do my export from Linked In and try to invoke the Macro, nothing happens. Microsoft wants me to put the Book1 into the same folder where the Download appears. That doesn't seem to work either. I don't know how or where the original Macro was stored so as to allow it to perform its functionality on every new Download. It's as though it were a hidden file that is just there all the time. Does anybody have any clue as to how that should be done?

 

[clifford_cooley]

The simplest two options:

1. If you export Linked In to the same workbook each time, store your macro in this workbook.
2. If you export Linked In to a new workbook each time, store your macro in personal macro workbook (personal.xls*).

*which is the hidden file you mentioned, that also contained your macros.

 

[9januerry9]

Where do I find the option to Store Macro In Personal Workbook? I am attaching the ribbon and part of the code from the Visual Basic icon on the Developer Ribbon.

 

[clifford_cooley]

The easiest way would be to record a new macro. And have the new macro saved in either this workbook or the personal macro workbook. Then you can copy you code to the new macro.

However you said that the code was already copied to a new workbook. You should be able to work from that workbook, as the code should already be in that workbook.

 

[wegmand]

Where do I find the option to Store Macro In Personal Workbook? I am attaching the ribbon and part of the code from the Visual Basic icon on the Developer Ribbon.

You should be able to export the code to a .bas file and then import it into a different workbook:

On the Visual Basic ribbon, under the File menu, choose "Export File..." and save the code somewhere (like the desktop) with a .bas extension. Close the Visual Basic editor.

Next, open the workbook where you want to use the code, Under the 'Developer' tab, open the Visual Basic editor (first button on the left). On the Visual Basic ribbon, under the File menu, choose "Import File..." and select the .bas file from its saved location (e.g.Desktop), click OPEN and then close the Visual Basic editor.

To verify your code is there, just click the "Macros" button on the 'Developer' tab. It's title should show up on the list.

 

[9januerry9]

OK! I am soooooooo confused. Here's what I have done so far. I created a simple BoldMe Macro and stored it in the Personal Workbook. Easy so far. That Macro appears when I open Excel and go to View, click on Unhide and the Personal Workbook shows up with the BoldMe Macro therein.

Now I have this other Macro code that I have saved in a NotePad text file. How do I get that code into the Personal Workbook step by step. I tried it once and got it in there. It appeared as Sheet1.FormatLinkedInExportFile. I then clicked Hide and saved it in the Personal Workbook. However, when I tried to invoke the Macro the next time I exported from Linked In to a .csv file, it didn't work. So I am now back to the Personal workbook having just the BoldMe Macro. HELP, Please!!!!

 

[wegmand]

OK! I am soooooooo confused. Here's what I have done so far. I created a simple BoldMe Macro and stored it in the Personal Workbook. Easy so far. That Macro appears when I open Excel and go to View, click on Unhide and the Personal Workbook shows up with the BoldMe Macro therein.

Now I have this other Macro code that I have saved in a NotePad text file. How do I get that code into the Personal Workbook step by step. I tried it once and got it in there. It appeared as Sheet1.FormatLinkedInExportFile. I then clicked Hide and saved it in the Personal Workbook. However, when I tried to invoke the Macro the next time I exported from Linked In to a .csv file, it didn't work. So I am now back to the Personal workbook having just the BoldMe Macro. HELP, Please!!!!

Ugh, I'm stumped! But there may be some useful nuggets on this page:
http://smallbusiness.chron.com/convert-excel-sheets-csv-macros-45270.html

The issue is different but steps 3-5 may contain clues you can use.