import txt file issue with 2010

[oddworld]

Ok, at our work we have recently moved from excel 2003 to 2010. I have several workbooks that contain macros that look for different txt files from a users desktop and imports them and then does stuff. For example in 2003 I use the below:

Workbooks.OpenText Filename:="C:\Desktop\ADOABS19.TXT"

However in 2010 the same workbook errors "file could not be accessed". I recorded a macro file import in 2010 and it returns the following:

ChDir "C:\Users\users.name\Desktop"
Workbooks.OpenText Filename:="C:\Users\users.name\Desktop\ADOABS19.TXT"

I understand that 2010 is now including the users name, my issue is that my workbooks are shared, so whilst I can record it in my name it won't work for another user, does anyone know who to make this for multiple users?

Regards,
Odd

 

[jimrward]

Try google with excel vba specialfolders and you should be able to return the string pointing to a users desktop as defined in a particular machine setup

 

[oddworld]

thanx jimrward, the solution I have found that works is to use the below function:


'Options For specical folders
' AllUsersDesktop
' AllUsersStartMenu
' AllUsersPrograms
' AllUsersStartup
' Desktop
' Favorites
' Fonts
' MyDocuments
' NetHood
' PrintHood
' Programs
' Recent
' SendTo
' StartMenu
' Startup
' Templates
Function SpecialFolderPath() As String

Dim objWSHShell As Object
Dim strSpecialFolderPath

'On Error GoTo ErrorHandler
' Create a shell object
Set objWSHShell = CreateObject("WScript.Shell")
' Find out the path to the passed special folder,
' just change the "Desktop" for one of the other options
SpecialFolderPath = objWSHShell.SpecialFolders("Desktop")
' Clean up
Set objWSHShell = Nothing
Exit Function
ErrorHandler:

MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"
End Function



Then within the worksheet:

'ChDir SpecialFolderPath
Workbooks.OpenText Filename:=SpecialFolderPath & "\ADOABS19.TXT",

 

[Norie]

Odd

Was it only Excel that changed version?

 

[oddworld]

I am not sure Norie, I just noticed that my excel "shared" macros stopped working when we upgraded to 2010.