Free Microsoft Excel 2013 Quick Reference

CLosing Excel 2007 with personal macro workbook

When I close Excel 2007, all open worksheets are closed with a Save Changes
prompt where needed. However, Excel itself doesn't close. It requires a
second click of the close button. It took a little research to discover that
this behavior is due to the presence of the Personal.xls macro workbook,
which I need. It seems I now have to click Close twice or hold the shift key
while closing Excel if I want to use Personal.xls, even if none of my macros
have changed. No other Office app behaves this way, nor has Execl ever done
this before 2007.

Is there a setting or fix/workaround/hack to prevent this behavior? It's
very irritating.
--
Kato Wilbur


Hi -

I am using Excel 2003 SP2 and just created a personal macro workbook.
The workbook is saved in the XLSTART folder. Everytime I close Excel,
it prompts me to save the workbook. Is there anyway I can stop this
from happening everytime I close Excel?

I searched previous postings on this subject and the few things I
found did not work. Any help would be greatly appreciated.

Thanks,
Stephen

Hello,

My company is running Navision v.5 and Office 2007. There are a few reports that were setup by the vendor to export directly from the ERP to Excel. The problem is, we can't write any macros in the personal macro workbook, it is unavailable when using these reports. The vendor says this can't be corrected. We'd like to write some formatting macros but can only store them in the workbook which will be different each time the report is run. Has anyone else encountered this or know of any workaround?

Any help is appreciated.

I have a several VBA procedures that read and process large (+50M) data
files. I am trying to do everything I can to optimize the speed.

Up until now, I was unfamiliar with "personal macro workbook". I would like
to use it for some of the macros that I use daily, but are stored in
individual workbooks.

If I starting using the "personal macro workbook", when I process my my
files, will it slow my programs down even more?

I currently am using Excel 2003. Thanks in advance

Hi,

I have this problem where I created some macros saved in a personal macro workbook. Excel 2007.

And ever since, I can never close excel. I close the spreadsheet. Excel is still open but empty. I have to close it one more time to actually close. This is because of the hidden personal macro workbook.

How do I get around this?

Also, where can I find my personal macro workbook... on Vista... I tried searching, but can't find it anywhere. It has important macros and I would like to back it up.

thanks.

Hi,

I was wondering if anyone here has come across this problem. I have an
extensive personal.xls file that reads into excel at startup with no
problem. The problem is that I'm constantly getting asked if I want to
save personal.xls upon exiting even when I do not alter my macros. I
can open excel and simultaneously close it with no steps in between and
get the following popup,

"Do you want to save the changes you made to the Personal Macro
Workbook? If you click Yes, the macros will be available the next time
you start Microsoft Office Excel."

Any insights will be greatly appreciated.

Steve

Personal.xlsb will not open when I start Excel, neither from the shortcut on the taskbar nor from the Startup menu. I've attempted to record a macro and save to the Personal workbook, but I get the following message: "Personal Macro Workbook in the startup folder must stay open for recording" and then I get the next message that reads "Unable to record macro." the Personal.xlsb is in the correct location of:

C:UsersRachelAppDataRoamingMicrosoftExcelXLSTART

any thoughts?

thank you,
Rachel

Hi,

Does anyone know if it's possible to have a Personal Macro Workbook do certain steps whenever a file is opened or closed? For example, if I opened a file, I'd like the Personal Macro workbook to automatically start up and do some stuff. Then when I close the file, I'd like the Personal Macro Workbook to again do some more stuff, save the file and then close it. I'd like this to happen without the user having to click any buttons or keystrokes.

I experimented with Auto_Open and Auto_Close, but since that's tied to the Personal Macro Workbook it only works when the Excel program is either started up or shut down.

Thanks for looking at my questions.

Jay

It's easy to close an excell workbook with a macro but is it possible to close with a macro the excel program itself?

Thanks
Theuns

Hi,

I was wondering if anyone here has come across this problem. I have an
extensive personal.xls file that reads into excel at startup with no
problem. The problem is that I'm constantly getting asked if I want to
save personal.xls upon exiting even when I do not alter my macros. I
can open excel and simultaneously close it with no steps in between and
get the following popup,

"Do you want to save the changes you made to the Personal Macro
Workbook? If you click Yes, the macros will be available the next time
you start Microsoft Office Excel."

Any insights will be greatly appreciated.

Steve

I'm missing the the personal macro workbook option in excel?

Yet another question on this topic. I cannot create a macro in my personal
macro workbook as it comes up with the error message "Personal Macro Workbook
in the startup folder must stay open for recording". I have searched c: for
the personal.xls file and it doesn't exist - checked start up folder and not
there. I have detect and repaired excel. I have checked disabled items. I
have checked hide/unhide. Can anyone help me?? am I missing something obvious?

Cheers

Excel 2007 - error after saving workbook with external data

I've got a workbook with an external datasource, a table (view) from an SQL Server 2008, in it. When I added a column to my table/view and refreshed the data, the new column was found after the already existing columns.
Then I inserted a new column where I wanted the new column to be and moved the new column to this empty column. Refreshed the data and everything worked fine. Saved the workbook.
When I open the workbook I get the message "Excel found unreadable content in  . Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."
When I click Yes, I get a list of repairs. The vital one beeing Removed Feature: Table from /xl/tables/tables1.xml part (Table).
Then the workbook is opened, but the connection to my external datasource is lost.
What am i doing wrong?

Howdie,

Macro (below)runs fine in the book where it was designed.
Problem is I need to put it into another users personal Macro Workbook
so that they can open files, run the macro, then close the file.
I can see that the Macro is trying to run itself in the personal
workbook (it creates a new tab called Master, and this is being created
in the Personal Macroworkbook, instead of the active workbook)
I cant tell it to refer to a workbookname, as the names of all the
workbooks where it needs to run in will be different.

I appreciate all your suggestions!!

Macro code currently is

Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("Master").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = Lastrow(DestSh)

sh.Range("b9:p20").Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy
only the values
'or use the PasteSpecial option to paste the format
also.

'With sh.Range("A1:C5")
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With

'sh.Range("B10:h20").Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

DestSh.Cells(Last + 1, "p").Value = sh.Name
'This will copy the sheet name in the D column if you
want

End If
Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
MsgBox "The sheet Master already exist"
End If

Call Delete_specific_row
Call Country_name
Call Remove_data_validation
Call paste_values

End Sub

T H I S A L S O M A Y B E R E L E V A N T

Function Lastrow(sh As Worksheet)
On Error Resume Next
Lastrow = sh.Cells.find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

*** Sent via Developersdex http://www.developersdex.com ***

Yet another question on this topic. I cannot create a macro in my personal
macro workbook as it comes up with the error message "Personal Macro Workbook
in the startup folder must stay open for recording". I have searched c: for
the personal.xls file and it doesn't exist - checked start up folder and not
there. I have detect and repaired excel. I have checked disabled items. I
have checked hide/unhide. Can anyone help me?? am I missing something obvious?

Cheers

I recorded a macro in the personal macro workbook, and later deleted it.
However, whenever I start Excel now, the personal workbook loads. How can I
stop this?
Thanks

OK, so I have an extremely fickle and technologically helpless boss who is too lazy and doesn't ever remember how to maximize a workbook within a maximized Excel and then put filters on it. We have a report the is scheduled via our online system to generate every day, but the result is a brand new file each time, thus I can't put a macro in the file itself. What I want to know is if it is possible to put a macro in my Personal Macro Workbook that will run if a newly opened file is "Concrete name of the file.xls". Fortunately, as indicated, the file name is static and doesn't change with the date, so I won't have to deal with an ever changing file name. I understand that there's a fairly easy way to program a macro to run upon opening a file, as long as the macro is within that file, although I've never done such a thing. What I'd like to do is essentially the same thing, but have the macro stored in the personal file.

I accidentally closed the personal.xlsb window in VBA, and now I cannot reopen it. If I try to record something I receive the Error: Personal Macro Workbook in the startup folder must stay open for recording".
The personal.xlsb file is located in XLSTART as it should.
I tried to enable it in add-ins (excel options>add ins>Disable Item = Personal.xlsb) but after I enable it everytime I open excel It freezes.
I have no idea what is going on and I dont want to loose my codes. I hope I haven't yet.

Any Ideas of whats going on?

Please Help.

Hi

I am estimating to Extend a business system report engine in Excel 2007 with VBA and storeprocedures. My thought is that my Business system (ERP) has a key fields that isexported out into the Excel sheet but I am missing 5 fields in this particular report. This fields cannot be inserted by the standard report engine so I thought a buttonin Excel 2007 wil do the trick. So my question is wich oneof folloowing suggested approacheswill be the best out of a performance view:

1) A macro that iterates through the existing data in the Excel worksheet to get the key Identities so that a stored procedure can be run for every one af the rows in the excel sheet and add the 5 missing fields

2) Extract a big dataset from db instead of having a store procedure being executed for each and every row in the excel sheet. This dataset will then in turn be iterated by the macro so the missing 5 fields can be put ineach correct row by the macro

3) any other suggestions?

i have problem opening the mentioned WB

it is not found in XLStart folder

i tried to make macro and choose to save it in personal macro workbook, but the Excel keep giving this message:
*personal macro workbook must be open
*unable to record

however, to save macros in the other WB, is ok
i even went to option>general. at start up....could not find any file

pls help

I have a number of spreadsheets I created with Excel 2002. Today, I installed Excel 2007 in its own folder. I want to continue to use Excel 2002 with my older sheets because of macros and VBA code that they contain; and Excel 2007 with newer ones I create.

Is this possible?

Thank you.

Every once in a while when attempting to open Excel the program freezes. I battled this problem yesterday and thought I resolved it (by restarting and giving it ample time to open on its own). However this morning I discovered that as consolation Excel appropriated the macro workbook. I found copies of it in several places (XLSSTART folder, trash bin, etc.).

I would be very grateful if someone could give me pointers on how to rein in this rambunctious program and get my macros back. Perhaps I've mistreated it by not fully optimizing its performance, this I suspect because when I open large files Excel as a program fails to start up but when I start the program and then open a workbook it performs satisfactorily. Also, when I record macros they get saved in a module within the active workbook, not personal macros workbook.

Thanks in advance!

PS. I'm on Vista/Excel '07

i am building a large spreadsheet with a form containing many text and comboboxes blah blah. Every once in a while I will get a message that pops up when I try to open the workbook and it says something about "the personal macro workbook is locked for editing" and then gives options to open the workbook in a read-only mode. The workbook I am building has nothing to do with my personal macro workbook, which has many macros in it. What is this message and why is this happening? Tonight was scary because i got a a debug box and memory error message prior to getting the personal macro message.

Hi,

I wrote a code in a module which is in a workbook. I wanted it to be available for other workbooks, so I copied it into the Personal Macro Workbook. The code stopped with the following error msg:

Run-time error 91, Object variable or With block variable not set

Monitoring with Print.Debug I tracked down the problem to the following section in my code:
--------------------------------------------------------------
Set TorqueRange = ws.Range("A8", "A" & nLastrow)

Debug.Print TorqueRange.Address

minT = Application.WorksheetFunction.Min(TorqueRange)
maxT = Application.WorksheetFunction.max(TorqueRange)
Debug.Print "minT"; minT
Debug.Print "maxT"; maxT
--------------------------------------------------------------
the problems is that the min and max functions do not work, they do not assign a value to minT and maxT meanwhile the TorqueRange is correct.

If I keep the original workbook open, in that case that code works ok on other workbooks but I would like to be able to run it from the Personal Macro Workbook.

Any advise would be greatly appreciated!

Many thanks in advance!

Zoltan

I was writing a macro in my personal macro workbook while another excel file was open. I saved my work in the personal workbook and then quit excel. A msg popped up to ask me if I wanted to save the other workbook and I said no.

The problem now is that when I open excel, I can't see the personal workbook. When I try to record macros in it, I get a message that says "Personal macro workbook in the startup folder must stay open for recording."

How do I get my personal macro workbook back and what did I do to make it go away?