VBA Quicky Help

[Scott]

I am creating a little program to help me out at work. Using Excel I am trying to create a link so that when a button is clicked a Word document is opened. So far I have this..

 

AppActivate Shell("C:\Program Files\Microsoft Office\Office\Winword.exe C:\Test1.Doc")

 

Now this works fine. However, if I want the document to be in a folder on the desktop the code should read..

 

AppActivate Shell("C:\Program Files\Microsoft Office\Office\Winword.exe C:\Documents and Settings\uBlahblah\Desktop\Calibration\Test1.Doc")

 

This doesn't work due to the spaces in "Documents and Settings". I have tried putting in "" on either side of the path but it rejects it. Some of the filenames I wish to open also have spaces.. same issue.

 

I'm sure it will be a simple fix but I can't find it at the mo.

 

Can anyone help?

[JustGav]

Try reference with one of the internal windows %USERDIR% or something like that... I'm no VBA guy so probably talking out of rear end or what about changing " " to %20?

[Scott]

Tried %20 for spaces but that didn't work either, URL's only I think

 

Will try the %USERDIR% idea though.

[Martin J]

Does this help?

 

http://www.ozgrid.com/forum/showthread.php?t=50453

 

Been a while since I did any VBA, but I remember it being a pain at times. Using the variables etc in the link rings a bell though.

[Attero]

Haven't used since VBA since college.

 

Still don't know it.

[Smallshinyant]

have you tried using the short name for the directory? i believe its Docume~1 but i forget.

[Scott]

Thanks guys, keep em coming.

 

I did think of that Shineyant, I just hoped there would be a way round it as I would need to look up the short names of all the files that have spaces in them.

 

Cheers

[Smallshinyant]

its seems it may be an issue with VBA and long file names, there maybe an API to work around it.

 

here is a function that will convert to short file names.

 

Private Declare Function GetShortPathName Lib "kernel32" Alias "GetShortPathNameA" (ByVal lpszLongPath As String, ByVal lpszShortPath As String, ByVal lBuffer As Long) As Long

Public Function GetShortPath(strFileName As String) As String

'KPD-Team 1999

'URL: http://www.allapi.net/

'E-Mail: [email protected]

Dim lngRes As Long, strPath As String

'Create a buffer

strPath = String$(165, 0)

'retrieve the short pathname

lngRes = GetShortPathName(strFileName, strPath, 164)

'remove all unnecessary chr$(0)'s

GetShortPath = Left$(strPath, lngRes)

End Function