Problem with ' in filename

[rbrhodes]

Hi,

Using function with ExecuteExcel4Macro to extract data from a 'closed' workbook. It's a common find on the net and works fine unless there's an 'Irish' name in the path or file, e.g. O'Connell or the like. Seems to me that a ' is not an illegal character but the function fails...

I'm using FileScripting to rename the file etc, so the problem has been worked around, just curious tho:

Anybody else run across this?

:patty:

[Bob Phillips]

Have you tried escaping the apostrophe, perhaps add another?

[rbrhodes]

Hi xld,

Yes I did try a few things including that. As I mentioned I made a work around. I guess I'm now wondering what other 'semi' legal chars I might run into! As this is in a piece of software that's to be distributed I guess I'll find out from users!

Gonna mess with it myself some more tho first.

[Simon Lloyd]

rb, are you running a MAC?, here's some code from Michael Bauer (MVP - Outlook) Sub ReplaceChars(Text$)
Dim ar()
Dim i&
Dim ReplaceBy$
ReplaceBy = ""
ar = Array(";", ":", ",")
For i=0 To Ubound(ar)
Text = Replace(Text, ar(i), ReplaceBy, , , vbTextCompare)
Next
End Subthere's an entry in our kb too http://www.vbaexpress.com/kb/archive.php/k-778.html

another illegal character in a filename would be " which in a lot of countries would be dislayed as ' so aren't they one in the same when it comes to filenames?

[GTO]

Hi Dusty,

The "slow typist" here; not wishing to waste your time, but if in Windows, I tried doubling (escaping?) the apostrophes as Bob mentioned. Seemed to work.


Option Explicit

' Derived from: http://www.vbaexpress.com/kb/getarticle.php?kb_id=454
' By: johnske
Sub GetIrishData()
Dim _
strPath As String, _
strFName As String, _
lRow As Long, _
lCol As Long, _
strAddress As String

strPath = ThisWorkbook.Path & "\O'Malley\"
strPath = Replace(strPath, "'", "''", 1, -1, 1)
strFName = "O'Book1.xls"
strFName = Replace(strFName, "'", "''", 1, -1, 1)

For lRow = 1 To 10
For lCol = 1 To 10
With Sheet1
strAddress = .Cells(lRow, lCol).Address
.Cells(lRow, lCol).Value = GetData(strPath, strFName, "Sheet1", strAddress)
End With
Next
Next
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function


Mark

[rbrhodes]

Hi folks,

PC for sure. Good one tho!

Simon, iInterestingly the 'BadChar' function you pointed out in the KB only defines 5 of the 9 MS official bad chars but adds 2 of it's own that are not in the MS set. Go figure.

What I've got so far:

The official MS set is / \ : * ? " < > |

The VBX KB function looks for / \ : * ? plus [ ]

I read that FAT sytems don't allow ^ but who cares anymore... NTFS <g>

There's a KB article which contains a copy of the function I'm using (like I said tho it's common).

http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

and here's the part I'm using:


Private Function GetData(Path, File, Sheet, Address)

Dim Data$

Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)

GetData = ExecuteExcel4Macro(Data)

End Function


Notice it constructs an <arg> and then runs ExecuteExcel4Macro (arg)

What I found (and that started this thread) is that the function was choking on a damn apost'rophe The file name was O'Connell but it turns out it doesn't matter if the apostrophe is in the path name or the file name or both = <GACK>.

After testing every possible combination I found that it will choke on any of the MS official 9 'bad chars' PLUS the 2 added in the VBX function PLUS my I'rish names. Caret^s, and all other chars are fine are fine! eg: This file name was no problem:

O`~!@#+$=,%;^&()_-{}.Book1.xls

Note the ` from the ~ key, etc

So in conclusion I suggest:

1) The BadChar function should probably include at least the MS9, plus the [ ]

2) The GetData from closed workbook function, here and everywhere else on the web should warn about the MS9 plus the VBX2 and the dr1

MS9 / \ : * ? " < > |
VBX2 [ ]
dr1 '


Hey Arizona,

Slow typist? You wrote and tested that code while I was typing (and correcting the typos) this!

I tried messing with "escaping" the ' and it worked a time or two and then repeatedly crashed Excel 2003 and 2007. To the point where Task Manager would not end it and I had to go into processes to kill Excel without a reboot. Your version is slightly different from mine tho so I'll try yours as well, hey its WinDoze after all!

Thanks all!

EOR

(end of rant)