import txt file issue with 2010

oddworld

Active Member
Joined
May 31, 2005
Messages
250
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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",
 
Upvote 0
Odd

Was it only Excel that changed version?
 
Upvote 0
I am not sure Norie, I just noticed that my excel "shared" macros stopped working when we upgraded to 2010.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top