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

Free Microsoft Excel 2013 Quick Reference

Hyperlink File Path Too Long?

I have a database saved as an excel spreadsheet that includes hyperlinks to AutoCAD drawings on my hard drive.

My database worked fine until I recently decided to reorganize my files into more specific folders. Now, when I update my hyoerlinks to the newer and longer file paths, they don't work.

An example of an old file path is:
C:Program FilesAutoCAD LT 2000Drawing FilesAvalon HseBLSouthBLSAva51FBA001FTG.dwg

The same file at its new address is as follows:
C:Program FilesAutoCAD LT 2000Drawing FilesAvalon HseBLSouthFBAVersion #5BLSAva51FBA001FTG.dwg

When I use the hyperlink function, it works fine using my old path, and it gives me an error with the new path that reads "cannot find specified file." I am positive that the file path is spelled accurately as I copied and pasted it from the path in Windows Exploer that points to the given file.

Is there something in Excel that limits the length of a file path in hyperlinks? I sure hope there's a way for me to get this database working without having to go badk to my old filing system.

Jearshub


Post your answer or comment

comments powered by Disqus
http://LongPathTool.com

Long Path Tool recognizes the processes or running programs which
have
locked those files, preventing them from being renamed, moved or
deleted.
The following are some of the latest changes:
Copying folders and files omitting the error message: File name too
long error
Demonstration of deleting path too long
Delete files from drives with mapped network
Delete files following reboot
Lists the paths and files with characters more than 200 in length
Vista support for mapped disks
Vista administrator support

In addition to deleting or copying files and folders, this utility is
also used to control unsolicited business spam or email messages.

Long Path Tool carries a stringent Anti-Spam Policy barring the use
of
computer user’s accounts to send unsolicited mail. This program will
not rent, sell or lease email subscriber listing to third parties.
Whilst there is a continued effort to actively evaluate and implement
brand new technology, including expanded features for filtering,
there
is no technology available currently that will completely prevent the
receiving and sending of unsolicited emails. But, if you are using
the
right tools and being careful about sharing your email address online
this could help to reduce those unsolicited messages.

http://LongPathTool.com

Large Excel 2007 files take too long to open

Opening an excel 2007 file of about 24 MB size is taking about 25~30 seconds. The file is a data dump and just has a lot of data in a single sheet. It does not contain any macros or formulae/calcs etc.

If I convert the same file(do a Save As) into an Excel 2003 file (.xls) it opens up very quickly.
Can anything be done to reduce the time to open such large files in Excel 2007 format (.xlsx)?

http://CannotDeleteFile.net - Cannot Delete File? Try Long Path Tool

Filename is too long?

Computer Complaining Your Filename Is Too Long? The Long Path Tool
Can
Help

While most people can go about their business blissfully unaware of
the Windows Explorer path length limit, others are not so lucky.
Everything seems normal until one day you download a file or create a
new folder in a directory and try to use it, and it happens. You try
to open or delete your file and the annoying “path too long” appears
on screen, telling you that Windows Explorer can’t handle the file
because the path is longer than the allowed length.

You see, while Windows itself can handle paths up to 32,000
characters
long, Windows Explorer can only handle up to 256 characters. This
means that you need a special path too long utility to make your file
usable again. The Long Path Tool is one such utility that will allow
you to deal with your files or folders that are triggering the “path
too long” message. What exactly can it do?

Well, first of all it will let you delete these files or folders if
you just don’t want to deal with them anymore. The most annoying
thing
about the Windows Explorer path limit is that it won’t even let you
delete the files or folders that are causing problems; it just gives
you an annoying “cannot delete file” message. With the Long Path
Tool,
you can delete an undeletable file or folder easily.

Of course, you probably won’t always want to delete the file.
Sometimes you just need whatever’s in it. Fortunately, the Long Path
Tool also allows you to rename files or folders, or copy or move them
around so that Windows Explorer will stop giving you the “path too
long” message. It’s a quick process that will make your files
functional again.

So, if you’re getting the “path too long” message or the “cannot
delete file” message, and need a utility that will quickly eliminate
the problem, the Long Path tool is the way to go. You’ll be able to
delete or rename your files in no time.

http://LongPathTool.com

Hello all

I guess this is a real challenge... and I'm almost finished but not the last bit.

I am trying to import a csv file, but only the first "column" of it. Here is a part of it:

=========================

26-09-2008,"NAAR RENTEMEERREKENING","5075036","","GT","Af","80,00","Girotel",""
25-09-2008,"TradeDoubler B.V.","5075036","651328756","OV","Bij","84,08","Overschrijving"," TRADEDOUBLER PAYMENT 0809"
23-09-2008,"KN: 601472976724","5075036","451146255","IC","Af","2,43","Incasso"," BIBIT INTERNET PAYMENTS ORDER*1-6046260 GREETZ"
22-09-2008,"Naam: HOFMAN - GOOSSENS","5075036","IBAN: BE70103222639825","","Bij","4,40","null"," Kenmerk: NOTPROVIDED Omschrijving: wil lid worden van vereniging(Dimit ri Hofman)"
18-09-2008,"J G C Wagenaar OSSENZYL ","5075036","895983","GT","Bij","5,00","Girotel"," J G C Wagenaar OSSENZYL Geld voor ver.lid voor Probleem pjes TRANSACTIEDATUM: 18-09-2008"
18-09-2008,"NAAR RENTEMEERREKENING","5075036","","GT","Af","1250,00","Girotel",""
17-09-2008,"P C BAKKER UTRECHT ","5075036","6304545","GT","Bij","50,00","Girotel"," P C BAKKER UTRECHT Contributie PC Bakker (Nozel) + schenking TRANSACTIEDATUM: 16-09-2008"
17-09-2008,"Naam: Ciao GmbH","5075036","IBAN: DE60700700100166668400","","Bij","1200,00","null"," Kenmerk: NOTPROVIDED Omschrijving: K-703562 Invoice 2056"
16-09-2008,"G M Pattiwaël van Westerloo","5075036","1667718","GT","Bij","10,00","Girotel"," Bijdrage nero38 TRANSACTIEDATUM: 16-09-2008"
15-09-2008,"DONOD B V","5075036","612012611","GT","Af","69,14","Girotel"," Nod32 Nederland factuur 20822725 debiteur 19001 datum 15-09-2008"
09-09-2008,"B.M.A. WILLEMSEN EO","5075036","110741110","OV","Bij","54,25","Overschrijving"," ESEST SMART SEC. 20751001 FACT. NR. 2053"

=========================

Using the code below, I can import the dates (at the beginning of the sentence), but the dates of the sentences that are too long (I suspect) do not come in the sheet.

=========================

Sub ReadTextSub()

Dim TextA As Variant
Dim NumRows As Long

TextA = ReadText("C:read in.csv")
NumRows = UBound(TextA) - LBound(TextA) + 1

Columns("A").ClearContents
Range("A1").Resize(NumRows, 1).Value = TextA

End Sub

Function ReadText(FName As String) As Variant

Dim FileNum As Long
Dim TotalFile As String, TextA() As String
Dim Records() As String, NumRecords As Long
Dim i As Long

Dim NextPos As Integer
Dim sDatum As Variant
Dim s As String

FileNum = FreeFile
Open FName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))

Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)

NumRecords = UBound(Records) - LBound(Records) + 1
ReDim TextA(1 To NumRecords, 1 To 1)

For i = 1 To NumRecords

s = Records(i - 1)

If Len(s) = 0 Or InStr(UCase(s), UCase("Datum")) > 0 Then

TextA(i, 1) = ""
Else
NextPos = InStr(s, ",")
sDatum = Mid(s, 2, NextPos - 2)
TextA(i, 1) = sDatum
End If

Next i

ReadText = TextA

End Function

=========================

This is really weird. The output is:

26-09-2008
25-09-2008
23-09-2008
22-09-2008

18-09-2008

17-09-2008

09-09-2008

So some dates are missing. Anyone who can finish this?

Thanks in advance!

Wigi

I have an excel workbook (Office 2000) which contains a column of
hyperlinks on several sheets. To word documents that were organised in
a file tree like the following example:

c:DOCUMENTSSHAREDIMAGES2003MARCHitem_serial_ mar_03_001

There are many files linked to this path or similar, the only
differance being the 'Year or Month' folder. As per the date that the
image was taken on. There are around 300 files all varying in months/
years, all hyperlinked succesefully.

The spreadsheet displays the hyperlinks as 'item/serial/mar/03/001' in
each cell.

The file path has changed slightly to:

d:DOCUMENTSSHAREDIMAGES

Now the Spreadsheet and ALL of the files regardless of year/month are
stored in this folder, so we've effectivly removed steps 4 and 5 of
the file tree. (The year and month) This has, as expected broken all
of the hyperlinks. Upon moving the files they now default link to d:
but still retain their now non-exsistant year and month sub folder
steps.

I was hoping there would be someone able to shed light on what is
required in order to 'fix' all of the hyperlinks on a sheet by way of
VBA code. Be it one that removes steps of a file path or just
replacing the current ones with d:DOCUMENTSSHAREDIMAGES but
retaining the filename as they haven't changed. A further point to
note with this is that the text displayed in the cells is in the
forwardslash format whereas the files are named with underscores.

I've tried David McRitchies site with no luck and trawled here and
found similar threads but none that have worked.

I eagerly await some guidance. Cheers.

Our network drive has been upgraded and now the full path to linked
workbooks in our master workbook are much shorter (less than half the
length) than they used to be. When we are prompted to update some of
the links to workbooks, we are getting the message "formula too long"
and it appears to be relating directly to the formula's containing the
linked document paths.

Hi All,

I have some code traversing a directory of folders and subfolders on a shared server and returning a list of those file paths. Around 6000 folders with about 2 subfolders on average.

This returns a list of 12000 filepaths but takes around 13-15 minutes to do.
Each path looks something like this:

C:Folder1
C:Folder1Folder1SubFolder1
C:Folder1Folder1SubFolder2
C:Folder2
C:Folder2Folder2SubFolder1
C:Folder2Folder2SubFolder2
...
...
etc
etc

Do you think this is taking too long to do?

Brad

I created a series of combo boxes to accept values from the user. These values are intended to be used to build the name of a file they want to see. I tried to set whatever gets selected in each of the combo boxes equal to a distinct variable and inserted each variable into the file path at the appropriate point. I then created a variable for the file path and inserted the file path variable into code that opens that file, copies a particular worksheet, and pastes it into a new sheet in the “master” (the one with the form) workbook and labels it according to what it was named before.

The problem I’m having is that Excel says it doesn’t recognize the file path I’ve given it. Judging from the file path it quotes in the message, it’s not seeing my variables (only the part of the file path I entered in quotes) and then, later when I “debug” it says the variables are not defined.

I’m wondering if there is more to defining a variable than what I’ve done. On the form page of code, I declared the following variables:
The UserForm Code

	VB:
	
 
Public strPerformBySubtask As String 
Public strPerformBySubelement As String 
Public strPeriod As String 
Public strMonth As String 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I populated each combo box ok and remembered to call the sub procedures below, show the form, hide the form, et al.

I think the problem might be in the sub procedures below that I thought would define the variables by setting the respective combo box output equal to a distinct variable I could use in the file path:


	VB:
	
 GetWhichDates() 
     'set the month variable equal to whatever is picked in the combo box
    strMonth = cboMonth 
End Sub 
 
Sub GetWhichSubtask() 
     'set the subtask variable equal to whatever is picked in the combo box
    strPerformBySubtask = cboSubtask 
End Sub 
 
Sub GetWhichSubelement() 
     'set the subelement variable equal to whatever is picked in the combo box.
    strPerformBySubelement = cboSubelement 
End Sub 

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

Question #1) Is this not how to get Excel to recognize the variable as the output of the combo box?

Question #2) Is this how to get Excel to see “1” when the user selects “Jan” and so forth:

	VB:
	
 ConvertMonthSelectedToNumber(strMonth) 
    Select Case strMonth 
    Case "Jan" 
        strMonth = 1 
    Case "Feb" 
        strMonth = 2 
… 
    Case "Dec" 
        strMonth = 12 
    End Select 
End If 
End Sub 

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


To finish my illustration… In a separate module page I created distinct variables to equal the worksheet, the worksheet name, and the file path and inserted the variables from the form as follows:


	VB:
	
strSheet = "Task Order" & strPerformBySubtask & "-" & strPerformBySubelement 
 
strSheetName = strPerformBySubtask & "-" & strPerformBySubelement 
 
strFile = "D:Documents and SettingsmccaralDesktopMP2 MONTHLY FINANCIALS" _ 
& strPerformBySubtask & "-" & strPerformBySubelement & "2007" & strMonth & ".xls" 

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


Then, I inserted the variables into a bit of code that in a test case (ie manual entry), would open up the file the user selected, copy a user selected worksheet, name it, and add it as a new worksheet to my master workbook:


	VB:
	
Workbooks.Open Filename:=strFile 
Sheets(strSheet).Select 
Range("A1:P15").Select 
Selection.Copy 
Windows("SUBCONTRACTS_MASTER_WORKBOOK_ii.xls").Activate 
Sheets.Add 
ActiveSheet.Name = strSheetName 
ActiveSheet.Paste 
Columns("C:O").EntireColumn.AutoFit 
End Sub 

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

Only trouble is, it doesn’t work when I use the variables.

Any insight would be greatly appreciated. I’m new to VBA and pretty confused at this point. I took a class not too long ago and so the information is just fresh enough to make me think I can almost understand what’s going on. Thanks for reading along at any rate.

Hi, all,

Assume a sheet with 500 Hyperlinks to 500 "Word" documents kept in C:ABCEEE

In each cell ONLY the Documents name is displayed.

The Administrator decided to change the name of the Path and it should read now: C:EFGEEE

( So, All 500 Documents will reside now in C:EFGEEE )

My question is:
How do I change all 500 Paths in the easiest way ?

(Find & Replace did not accomplish the task).

I tried to write a Macro and used the REPLACE Function but the Hyperlinks ADDRESS Property contains the File name only and NOT the path.

Here is what I tried:
===============

	VB:
	
 Micky() 
    For Each HL In Selection.Hyperlinks 
        HL.Address = Replace(HL.Address, "abc", "efg") 
    Next 
End Sub 

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

Any Ideas ?

P.S. In the Edit Hyperlink window I saw a scrolling Box called "find in"
where the path was shown - but I could not find that boxes VBA property in order to change the Path via my code.

The attached Pictures shows what I mean by "Find In:".
(Don't bother with the shown Path as it is an example only).

Thanks, Michael

I need an excel function with 'hyperlink function' that also links file path including file name from one cell to file extension in another cell.

Is it possible to put this all in one cell?

Any help would be apprecated....I have several links in a file. The files
were moved from one server to another. I can't access the old server. I
need to update the links to reference the files on the new server. The
source and destination files are both open. I clicked edit, links, change
source and selected the new file. I get an error that indicates the formula
is too long.

I think this is because the old path is in the formula.
=SUM('C:Customer ServicesMickeyxxxx PortfoliosMarketing
InitiativesSellout segmentationPlan Sellout4+8 fcst[Revenue Sellout -
Monthly GL Amounts - xxx Trns.xls]2006'!E7:E10)

How can I make the updates without editing the individual formulas.

Thanks

In my accounting software package, MAS200, I can attach files in a .pdf
format. I use this in the accounts payable cycle to attach shopping
documents, packing slips, invoices, etc.

We can export the path of these files into Sequel as a database field.

We are trying to run a report that exports from Sequel to Excel, and I'd
like to include the file path as a hyperlink to the file.

Does anyone know how to do this?

I am writing user manuals for a series of reports I produce and I'm using
hyperlinks to show where reports are stored. When I insert the hyperlink it
loses the full name of the file path and replaces it with the drive letter
that the report is stored on. (i.e. instead of
uup-rep-dx89MI_Reportsdailytest.xls the hyperlink is shown as
E:MI_Reportsdailytest.xls.)

Although the hyperlink will still work, it is not very practical for
somebody to follow the user manual if they have a print out because network
drives can be mapped to different drive letters.

Hope this makes sense and hope somebody can help!

Cheers

Hey, I hope this is the right place to post this...

Basically I'm putting hyperlinks into an Excel spreadsheet (using Excel 2002). They link to a different file in a shared network drive. However, when the file is sent as an e-mail, and opened by someone else, the file path has changed to the user's local C: drive. I've tried to set the hyperlink base to the folder path, and zipping the file before sending, but it still isn't working properly. I'm using Lotus Notes to send the files.

Any ideas on locking the file path so that it will still retain the proper file path when it is e-mailed?

Thanks.

****sorry about this, just got the problem fixed, changing the base worked. This thread can be deleted*****

Hi,

I have created a Userform that will open up a windows explorer box that will allow users to browse to certain documents. On selecting the document it pastes a file path into the spreadsheet as a Hyperlink, this I have managed fine.

What I'm struggling with is the loop, so if a user wanted to copy into the spreadsheet a second file path it just looks at the starting named range see's theres a value and moves to the next cell below and so on for 9 possible rows.

The problem is I'm just not able to get it to loop. If anyone could show me just a simple loop code that would be very much appreciated.

Thanks in Advance

Macro1

(I've raised this in a couple of other communities - sorry to duplicate but I
don't understand the procedure to cross-post)

We keep getting this error when trying to open Excel files from within IE.
It's to do with the path length of the file names being too long. I
understand 218 is the maximum number of characters when working in Office
but, according to a M/soft article I've just read
(http://support.microsoft.com/?scid=k...d=2509&sid=216) this
can all be fixed when working within IE - that is, Excel running inside IE.
I've followed the steps - adding the registry Dword TruncateFileName and
setting it to 1 then deleting temp files - but it's made no difference.

Any ideas?

Hey guys,

I decided to post here after all the guys at my office couldn't help me, the Excel rookies! I think it is a relatively simple problem to fix, pls help!

I have a cell with a formula that adds up the values of the corresponding cell in other worksheets :

"=sum([File 1.xls]Nil_Summary'!$F$13, [File 2.xls]Nil_Summary'!$F$13+[File 3.xls]Nil_Summary'!$F$13..." and so on.

The problem is, my formula includes the directory path and file name of the files I want to sum up, which is repetitive & takes up unneccasary space. I can only sum up 20 cells before Excel tells me that the formula is too long. I have 60 cells in total to sum up.

Can anyone help to shorten my formula? I will be ever grateful

Hi,

I have a situation where this happens:

Filename (without .xlsx extension) + directory path name = 217 characters

If i reduce the filename by say 5 characaters the file will save with an extension if i dont reduce the file name by 5 characters it wont save the file with the extension......and then i get a weird run time error...........i check the err code value and its ok.

any ideas?

Any help would be apprecated....I have several links in a file. The files
were moved from one server to another. I can't access the old server. I
need to update the links to reference the files on the new server. The
source and destination files are both open. I clicked edit, links, change
source and selected the new file. I get an error that indicates the formula
is too long.

I think this is because the old path is in the formula.
=SUM('C:Customer ServicesMickeyxxxx PortfoliosMarketing
InitiativesSellout segmentationPlan Sellout4+8 fcst[Revenue Sellout -
Monthly GL Amounts - xxx Trns.xls]2006'!E7:E10)

How can I make the updates without editing the individual formulas.

Thanks

Hi, I am using a simple "Workbooks.Open Filename:=" line in VBA to open a webpage from our internal work website. It works fine for some pages but for others I get the error "Cannot open the file because the specified file name is too long. Rename the file with less than 256 characters". Basically as you can see the URL is too long but since it changes each day (the report I am running chooses the last business day) I can't use something simple like TinyURL without plugging it into the website each day and generating a new URL. I have also tried a Function I found online (see below) but this doesn't work because it creates the same URL each time. Is there a way around this at all? I can't get work to simply reduce the size of the web address, believe me I would love that. Any help would be much appreciated!


	VB:
	
 
Sub testme() 
     
    MsgBox GetTinyUrl("http://www.longwebaddressgoeshere.../") 
     
End Sub 
 
[COLOR=#2060a0]Function[/COLOR] GetISGDUrl(url [COLOR=#2060a0]As[/COLOR] [COLOR=#2060a0]String[/COLOR])
[COLOR=#2060a0]As[/COLOR] [COLOR=#2060a0]String 
Dim[/COLOR] xml [COLOR=#2060a0]As[/COLOR] [COLOR=#2060a0]Object 
Set[/COLOR] xml = CreateObject([COLOR=#c03030]"MSXML2.XMLHTTP.6.0"[/COLOR]) 
 
xml.[COLOR=#2060a0]Open[/COLOR] [COLOR=#c03030]"POST"[/COLOR], [COLOR=#c03030]"http://is.gd/api.php?longurl="[/COLOR] & url,
[COLOR=#2060a0]False 
[/COLOR]xml.Send 
 
GetISGDUrl = xml.responsetext 
 
[COLOR=#2060a0]End[/COLOR] [COLOR=#2060a0]Function[/COLOR] 

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


Thanks,
Simon

This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB. Thanks for your help!


	VB:
	
 Range) 
     '-----------------------------------------------------------------
    Const WS_RANGE As String = "N:N" 
     
    Dim Cmnt 
On Error Goto ws_exit: 
    Application.EnableEvents = False 
    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then 
        With Target 
            If .Row > 3 Then 
                If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DLH" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 7 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "FDC" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 4 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "CJ" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 45 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "RT" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 20 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "GRR" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 22 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "TRG" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 54 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "GP" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 50 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DC" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 40 
                End If 
                 
                If Me.Cells(.Row, "N").Value = "" And Me.Cells(.Row, "O").Value = "JOINT" Then 
                    Set Cmnt = .Comment 
                    If Cmnt Is Nothing Then 
                        Me.Cells(.Row, "O").AddComment 
                        .Comment.Visible = True 
                        .Comment.Text Text:="COG MEs:" & Chr(10) 
                        .Comment.Shape.Select True 
                    Else 
                        .Comment.Visible = False 
                    End If 
                End If 
                If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "JOINT" Then 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 15 
                End If 
                 
                 'If Me.Cells(.Row, "O").Value = "JOINT" Then
                 'Set Cmnt = Me.Cells(.Row, "O").Comment
                 '    If Cmnt Is Nothing Then
                 '    ActiveCell(.Row, "O").AddComment
                 '    ActiveCell(.Row, "O").Comment.Visible = True
                 '    ActiveCell(.Row, "O").Comment.Text Text:="COG MEs:" & Chr(10)
                 '    ActiveCell(.Row, "O").Comment.Shape.Select True
                 '    Else
                 '    Cmnt.Visible = False
                 '    End If
                 'If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "JOINT" Then
                 'Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 15
                 'End If
                 'End If
                 
                If Me.Cells(.Row, "N") = "C" Then 
                    Me.Cells(.Row, "Q").ClearContents 
                End If 
                 
                If Me.Cells(.Row, "N").Value = "O" Then 
                    Me.Cells(.Row, "AS").Value = 1 
                Else 
                    Me.Cells(.Row, "AS").ClearContents 
                End If 
                 
                If Me.Cells(.Row, "N").Value = "C" Then 
                    Me.Cells(.Row, "AT").Value = 1 
                Else 
                    Me.Cells(.Row, "AT").ClearContents 
                End If 
                 
                If Me.Cells(.Row, "O").Value = "NO ACTION" Then 
                    Me.Cells(.Row, "N").ClearContents 
                    Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 48 
                End If 
                 
                If Me.Cells(.Row, "N").Value = "H" And Me.Cells(.Row, "A").Value = "" Then 
                    Me.Cells(.Row, "A").Value = Date + 30 
                End If 
                 
                If Me.Cells(.Row, "N").Value = "O" And Me.Cells(.Row, "A").Value = "" Then 
                    Me.Cells(.Row, "A").Value = Me.Cells(.Row, "C") 
                End If 
                 
            End If 
        End With 
    End If 
     
ws_exit: 
    Application.EnableEvents = True 
     
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub 
     
    On Error Resume Next 
    If Not Intersect(Target, Range("N:N")) Is Nothing Then 
        Application.EnableEvents = False 
        Target = UCase(Target) 
        Application.EnableEvents = True 
    End If 
    On Error Goto 0 
     
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub 
     
    On Error Resume Next 
    If Not Intersect(Target, Range("O:O")) Is Nothing Then 
        Application.EnableEvents = False 
        Target = UCase(Target) 
        Application.EnableEvents = True 
    End If 
    On Error Goto 0 
     
     
     
End Sub 
 
 'VBA Color Index

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


Hi all,

I have this formula which i know will work in theory, however it is too long, can anyone help me tidy it up at all? any help is greatly appreciated...

I realise it seems unwieldly but it is basically

=‘stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Swindon.xls]WC 180405'!B3 +....... repeated but with a different file each time, though they are all in the same folder, - im sure this must be the key
=‘stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Swindon.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Hemel.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Milton Keynes.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Pangbourne.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Llandarcy.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Stanlow.xls]WC 180405'!B3 +’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Avonmouth.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Sunbury.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Aberdeen.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Sullom Voe.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Teeside.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Dimlington.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Wytch Farm.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Coryton.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 St James.xls]WC 180405'!B3+’stockukBreak FixKPI Reports[ Break Fix KPi Report 2005 Britannic House.xls]WC 180405'!B3

I originally developed my project in Excel 2007 with a bunch of help (paid and free) from this forum...Thanks!
We've migrated to Office 2010 (and Excel 2010) and now when we try to run the Mail Merge from Excel, I get a "String Too Long" error from this line:


	VB:
	
 objMergeDoc.MailMerge 
    .MainDocumentType = wdFormLetters 
    .OpenDataSource Name:=strDataSrc, LinkToSource:=True, Format:=wdOpenFormatAuto, _ 
    Connection:="Provider=Microsoft.ACE.OLEDB." & Application.Version & ";User ID=Admin;Data Source=" & strDataSrc &
";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Engine Type", SQLStatement:="SELECT * FROM `ContractMergeData$`" 
    .Destination = wdSendToNewDocument 
    .SuppressBlankLines = 1 
    With .DataSource 
        .FirstRecord = wdDefaultFirstRecord 
        .LastRecord = wdDefaultLastRecord 
    End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The debugger points to the Connection line which is greater than 255 characters...about 390 with spaces to be exact.
What can I do about this?

Thanks!

Hi,

I have the familiar problem that my formula is too long. I've read that excel have a limit of 1024 characters in one formula which I exceed. Reason for why I exceed is that the cells are linked to other workbooks located on a web path, i.e. http://www.........
As this web path occupies a lot of characters, I would very much like to get around this path.

Does anyone know if it is possible to define a web path as a Name, e.g. MyName[Book1.xls]Sheet1!A1, where MyName refers to 'http://www......?

Thank you very much in advance for your assistance!!


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