Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Autorecover Location

A few years ago I set a custom location for AutoRecover files in excel. Now, following a few role changes, the path no longer exists on the network. When I try to change the location or select an tab in 'Tools/Option' I get an error "Cannot access directory blahblahblahAuto Recover Files"

Is there any way around this?

Tim


Post your answer or comment

comments powered by Disqus
Help,
I have about 30 pivot tables that all link to a workbook in the same folder, so the source data path is just the filename and range. Both files are on a network drive.

Today when I opened the pivot table file, all the pivot tables have had their source data changed to be the Autorecover location on the C: drive

Any ideas as it's a pain to have to go through manually 30 times and change the source data and I'm guessing it may happen again.

Thanks

Hi, I had some excellent help from several members yesterday evening so I'm hoping I'll get similar assistance today.

I'm having trouble with my electricity supply company. I've received an enormous bill, and my own readings and calculations taken over a period and recorded in Excel prove the bill to be wrong.

At this point I want to submit my results anonymously by email, and although I can remove my personal details from the "Properties" box I can't find a way so far of removing my details from the "AutoRecover" location, even if I turn "AutoRecover" off. I feel I'm missing a really simple solution, but I can't see it. Can anyone help? Thanks.

Hope someone can help!
I had specified an autorecover file location for an important worksheet a long time ago. Recent changes to reorganise the network servers at my company have left me with the autorecover file path looking for a non extitent server!!

I have tried the standard method of changing the path, but Excel will not allow any changes to the file options becuase the autorecover file location is unavailable. I have looked in several areas to see if there is another way to change this file location but I can't find one

CW

Description of the AutoRecover functions in Excel

Describes the AutoRecover functions in Excel. The AutoRecover ... Microsoft Office Excel 2007 The controls to configure the ... AutoRecover file location box but do not enter a new ...

I had a file open in Excel 2002 in which I made some changes, and then
mistakenly closed without saving. But after making those changes, and
before closing, it had remained open for over an hour.

I'd like to recover those changes, and the path specified in the
"AutoRecover save location:" text box in the Options - Save dialog tab is:

C:Documents and SettingsMyUserNameApplication DataMicrosoftExcel

However, I couldn't find a *.xls file in that directory. (The only thing in
that folder is a file named Excel10.xlb and another folder named XLSTART).

I don't understand why I can't find a backup file in that folder, because
the "Save AutoRecover info every X minutes:" settings are enabled (checked),
and set for every 10 minutes.

According to Excel Help for AutoSave, the file should be saved every 10
minutes in that directory. The "Disable AutoRecover" check box on that same
tab is disabled (unchecked), so it would seem the feature should work.

Is there any reason why the AutoSave feature would not be working as
described in Excel Help, and whether there is another location where it
might have saved the backup file?

Thanks in advance,

Paul

Hi,

I recently moved to a new computer. I transferred my MS Office settings
from the old computer to the new one. I had set the Autorecover save
location for an excel file on a different partition on the old PC. The
file opens fine on the new computer but under Tools>Options> when I try
clicking on any of the tabs, I get an error message that the partition
cannot be found! So everytime it tries to save an autorecover version,
it cannot and gives an error message, and does not allow me to change
the location! I was wondering if there was another way to change the
location of the default folder, autorecover, etc. I will appreciate any
help.

-ab

I recently installed office 2003 with SP3 on a machine for one of my users. He was previously using the 2000 version of excel. He tends to leave multiple workbooks open at the same time, and has made use of the AutoRecover feature in the past. Now after his workbooks have been open for a certain time, excel will give him a message stating that an unexpected error occurred and autorecover has been disabled for this session of excel. His save location for autorecover is on a network share, but it is the same one he was using prior to the upgrade. Also, when I log into his machine and go into the settings for AutoRecover, they are all grayed out. So I couldn't edit them if I wanted to. Is this issue familiar to anyone? Any help would be appreciated.

I had a file open in Excel 2002 in which I made some changes, and then
mistakenly closed without saving. But after making those changes, and
before closing, it had remained open for over an hour.

I'd like to recover those changes, and the path specified in the
"AutoRecover save location:" text box in the Options - Save dialog tab is:

C:Documents and SettingsMyUserNameApplication DataMicrosoftExcel

However, I couldn't find a *.xls file in that directory. (The only thing in
that folder is a file named Excel10.xlb and another folder named XLSTART).

I don't understand why I can't find a backup file in that folder, because
the "Save AutoRecover info every X minutes:" settings are enabled (checked),
and set for every 10 minutes.

According to Excel Help for AutoSave, the file should be saved every 10
minutes in that directory. The "Disable AutoRecover" check box on that same
tab is disabled (unchecked), so it would seem the feature should work.

Is there any reason why the AutoSave feature would not be working as
described in Excel Help, and whether there is another location where it
might have saved the backup file?

Thanks in advance,

Paul

Hi,

No matter how many times I change the default save location to my main folder (D:), the next time I open a document from an email attachement and choose "Save As" it reverts to My Documents. Then if I open a new file afterwards and try "Save As" it also reverts to My Documents. I have tried changing all the file location settings in the save menu to D: (Autorecover and Server drafts), and the settings are maintained when I close and reopen Excel. But still when I come to save something it goes to My Documents!

Any advice greatly appreciated!

Thanks

Hi,

I recently moved to a new computer. I transferred my MS Office settings
from the old computer to the new one. I had set the Autorecover save
location for an excel file on a different partition on the old PC. The
file opens fine on the new computer but under Tools>Options> when I try
clicking on any of the tabs, I get an error message that the partition
cannot be found! So everytime it tries to save an autorecover version,
it cannot and gives an error message, and does not allow me to change
the location! I was wondering if there was another way to change the
location of the default folder, autorecover, etc. I will appreciate any
help.

-ab


1. Select File -> Excel Options -> Save.
2. Change/update the path at Autorecover File Location text box.

Hi everyone,

I have a table of values, I have used the max function to return the highest value found. Now I need to know where that value lies, as the table is quite large. After reading through forums I've found a way for excel to return the cell location, but only for a single column or single row (Using the Address, Match and / or Index Functions). I need to find a way to do this for a table of values.

Any help would be much appreciated!

Thanks!

Hi there,

If I have a column of the following Text values, how do I identify the cell location of the last occurence of "Email *" in the range A1:A5 (ie. cell A5) ?
A1 : Race 1: $20,000
A2 : Email Race 1 to a Friend
A3 : Race 2: $5,000
A4 : Special Conditions
A5 : Email Race 2 to a Friend

I have been opening up pages for the last 2 hours and nothing seems to work. I am thiking it might involve a COUNTIF.
I tried the following but neither worked :
="A"&LOOKUP(2,1/(A1:A5="Email *"),ROW(A1:A5))
=SMALL(IF(SEARCH("Email *",A1:A5),ROW(A1:A5)),COUNTIF(A1:A5,"Email*"))

If anyone knows the answer to this, you would help me back my sanity.

Thanks,

Hi,

I have the following code to copy paste a range from an excel workbook to a specific location in a word document :


	VB:
	
 
Dim appWd As Word.Application 
Dim wdFind As Object 
Dim ClipEmpty As New MSForms.DataObject 
Dim ClipT As String 
Function IsClipboardEmpty() As Boolean 
    IsClipboardEmpty = (CountClipboardFormats() = 0) 
End Function 
Sub CheckClipBrd() 
    If IsClipboardEmpty() = True Then ClipEmpty.PutInClipboard 
End Sub 
Sub FormatPaste() 
    wdFind.Replacement.Text = "" 
    wdFind.Forward = True 
    wdFind.Wrap = wdFindContinue 
    wdFind.Execute 
    Call CheckClipBrd 
    appWd.Selection.PasteSpecial DataType:=wdPasteBitmap 
    CutCopyMode = False 
End Sub 
Sub CopyDatatoWord() 
    Dim docWD As Word.Document 
    Dim sheet1 As Object 
    Dim sheet2 As Object 
    Dim saveCell1 As String 
    Dim saveCell2 As String 
    Dim saveCell3 As String 
    Dim dir1 As String 
    Dim dir2 As String 
    Set appWd = CreateObject("Word.Application") 
    appWd.Visible = True 
    Set docWD = appWd.Documents.Open(ThisWorkbook.Path & "" & "webpage.htm") 
    Set sheet1 = ThisWorkbook.Sheets("Matrix") 
    Set wdFind = appWd.Selection.Find 
    ClipT = "  " 
    ClipEmpty.SetText ClipT 
    sheet1.Range("C2:F247").Copy 
    wdFind.Text = "111" 
    Call FormatPaste 
    docWD.SaveAs (ThisWorkbook.Path & "" & "TEST.htm") 
    appWd.Quit 
    Set appWd = Nothing 
    Set docWD = Nothing 
    Set appXL = Nothing 
    Set wbXL = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works fine, but the pasting is done and bitmap and I loose the table formatting

I would like to replace this line (in sub FormatPaste):


	VB:
	
appWd.Selection.PasteSpecial DataType:=wdPasteBitmap 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
With this line :


	VB:
	
appWd.Selection.PasteExcelTable 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In order to have a table that keeps its formatting within the word document.

The thing is, when I use the second line I get the error "Argument not optional".

Any idea what is causing this and how to solve it?

Thank you for your help

I have a macro called Master.xlsm that pulls data from several different workbooks located on my local drive. Master.xlsm is also located in the same local directory as the source workbooks. I'd like to move master.xlsm along with the source workbooks to a folder on SharePoint and be able to run the macro against the source workbooks located in the SharePoint folder.

Here's my current Code:


	VB:
	
 OpenSpecificSheet() 
    Dim arr As Variant 
    Dim sPath As String 
    Dim sFil As String 
    Dim strName As String 
    Dim owbk As Workbook 
    Dim twbk As Workbook 
    Dim i As Integer 
     
    Set twbk = ActiveWorkbook 
    sPath = "HOUIC-S-50913userCachedMy DocumentsMyMacros" 'Change to suit
    arr = Array("source1", "source2", "source3", "source4") 
    Application.ScreenUpdating = False 
     
    twbk.Sheets("Master").Range("C" & Rows.Count).End(xlUp)(3) = Date 
    twbk.Sheets("Master").Range("E" & Rows.Count).End(xlUp)(3) = Date 
    twbk.Sheets("Master").Range("G" & Rows.Count).End(xlUp)(3) = Date 
    For i = LBound(arr) To UBound(arr) 
        sFil = Dir(sPath & arr(i) & ".xlsm") 
         
        Do While sFil  "" 
            strName = sPath & sFil 
            Set owbk = Workbooks.Open(strName) 
            Range("B200:C200").Copy twbk.Sheets("Master").Range("c" & Rows.Count).End(xlUp)(2) 
             'owbk.Close False 'Close don't save
            Range("B201:C201").Copy twbk.Sheets("Master").Range("e" & Rows.Count).End(xlUp)(2) 
            Range("B202:C202").Copy twbk.Sheets("Master").Range("g" & Rows.Count).End(xlUp)(2) 
            owbk.Close False 
            sFil = Dir 
        Loop 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In the above I'd like the macro to look for the source workbooks in the following sharepoint location:
https://a101.sharing.company.com/sites/AUS01/R2/R2 Design/Design/Design Documents/RD - Customer/Test_Master

rather than looking in the local directory

Thanks for your time

I apologize, I'm struggling and those wiser than I on this forum may have a quicker solution.

I have a very nifty costing estimate spreadsheet I wrote for doing project cost estimates. I set it up to do some cool things BUT if someone wants to add a task (set of rows) and they don't know what they're doing it's very easy to mess up.

This is when I decided a macro to insert a blank standard "task" via button would come in handy. I'm having trouble because it's very easy to do once by recording a macro but the relative references get messed up when you try to run it again.

I have a dummy blank task/set of rows hidden at the bottom of my sheet. Relative references don't work because if you copy it and append to bottom of list of tasks the relative location isn't the same then next time you run the macro. SO what I think might work is I have text values hidden in the A column that are used for the SUMIFs in the subtotals section at the bottom. I'd like to create a macro that does the following:

1) for all cells in the A column = "Task X*" copy the entire row
2) insert entire selection of rows that meet criteria above 2 rows above the row in which the A cell = "Sum1"

I've tried combos of if thens and match and I'm missing someting...

This will appropriately append the blank task collection of rows (that have the nifty appropriate formula) to the bottom of the task section because the "Sum1" row is the beginning of the subtotals section. I figure if you get me that far I can alter it to also add a subtotal row for that task.

Thanks in advance

On regular basis I need to process a data dump file. The macro I have written to do so works ok, but I am trying to speed it up.
Screenupdating and Calculations are allready turned off during the macro.
I now try to avoid the .Select options, but am not sure if I am on the right track. Within the process I also need to move several columns to a different location.
This is what I started with:

	VB:
	
 test() 
    Columns("A:A").Select 
    Selection.Cut 
    Columns("F:F").Select 
    Selection.Insert Shift:=xlToRight 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I came this far:

	VB:
	
 test() 
    Columns("A:A").Cut 
    With Columns("F:F") 
        .Insert Shift:=xlToRight 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Am I on the right track, or is their a better (and or faster) way?

Hi, I have a Save As macro that saves my spreadsheet to a different location. My problem is after executing the macro, the new saved spreadsheet file is now the one opened. Can the macro be modified to still show the original spreadsheet file so that I can still continue to work on it?

Here's my macro:

	VB:
	
 SAVEAS()ActiveWorkbook.SaveAs Filename:= "C:Documents and SettingsDesktopBook1_" & format(now(),"YYYYMMDD") & ".xls" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
TIA!

I have a worksheet with toggle buttons that act on rows. Occasionally, I sort the rows differently, and the buttons will move accordingly - but since the row reference in the VBA code no longer applies, the buttons don't work appropriately.

Is there any way for a button to 'know' its row location - which I can then use instead of a static row reference - in VBA code?

Hey there!

Need to locate 3 columns in a sheet(the names of the columns are standard, the length of the columns may vary, but will always be the same length among the two columns), then make calculations between the cells of the same lines(ie A1*B1, A2*B2 etc, and A1*C1, A2*C2, etc, get the individual results in two different columns on the same sheet . Then get the sum of the two newly created columns and have those 2 results pasted in a different sheet in 2 specific cells.

Any help would be very much appreciated!

Thank you very much in advance!

Beste,

Giannis

EXCEL:2010

Morning all

Probably a simple request. I have dates in the format dd/mm/yyyy hh:mm:ss AM/PM in column E, sheet DATA and locations in column b,sheet DAT. I then have differnt tabs for each month of the year( January, February etc). In a summary field on each tab I need a count of all entries relating to the location based on the month specified in B6 (in format mmmmmmmmmm, yyyy) for each sheet. Hope that makes sense.

I'll try to explain the best I can.


	VB:
	
 Hyperlink) 
    If Target.Address = "/public" Then 
        Range("$C$16") = Range("C16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$F$16") = Range("F16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$I$16") = Range("I16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$L$16") = Range("L16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$R$16") = Range("R16") + 1 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code works fine but here is the thing:

Im trying to create a click counter to track what hyperlinks are clicked in my excel document.
If my excel document is located on the "public" drive, the only click counters that work are the ones located on the "private" drives.
For example: If my excel document containing the code above is located on the "public" drive the only IF statements that work are 2nd and 3rd.

If I copy the document and place it on the "Private" drive the only IF statements that work are the 1st, 4th, and 5th.

Creating a shortcut to files on the "public" drive and placing them on the "private" drive, (and/or Vica Versa), and hyperlinking to them does not work because the document thinks the shortcut may be unsafe.

I have macros referencing a group of merged cells that contain the hyperlink. The macros are placed on a image/picture.

So basically whats happening is:

Someone clicks a picture that has a macro on it, that macro references a group of cells that contain a hyperlink. Once clicked the counter increases by one.

Im just wondering why the click counters only work on some of the hyperlinks, depending on what drive my excel file is located on. If I flip the drives, the ones that previously didnt work, now work, and the ones that previsouly worked, no longer work.

I hope that makes sense.

Let me know if you need any clarification.

Thanks

Hi,

So I have a Userform with [ComboBox1] that has a list of months. I want to use the Find function to locate a column based on the [ComboBox1] Selection.

Example: If [ComboBox1] = Jan-12, and in [Sheet1] Jan-12 is the header of Column B, then the result of the Find function should be 2 (Since B is the 2nd column).

If someone can help me with the Find function that would be great, the rest of the code is working.


	VB:
	
 ConfirmEditsNonCC_Click() 
    Dim ws As Worksheet 
    Dim iColumn As Long 
    Set ws = Worksheets("NonCCExpenses") 
    ws.Activate 
     
     'Find correct Column in database
    iColumn = ws.Cells.Find(What:=ComboBox1.Value, SearchOrder:=xlColumns, searchdirection:=xlPrevious).Column 
     
     'Overwrite the data with the new edits
    If Len(TextBox1.Text) >= 0 And Len(TextBox2.Text) > 0 Then ws.Range(Cells(26, iColumn), Cells(26, iColumn)).Value =
TextBox2.Text 
     
     'Close Form After Confirmation
    Unload Me 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks!

Hi guys
Could anyone advise me on the best way to save my workbook to a secondary location please?
Company has a shared network folder on a "0" drive and I need my workbook saved to that location as well as my own "My Documents" folder.


No luck finding an answer? You could always try Google.