PDA

View Full Version : Multiple User Account Problem (maybe)



Confused
09-28-2005, 02:23 AM
Hi,

I have a problem with a VB script that works on a PC but not on a MAC.

I have made changes to the script as per the help instructions. I have modified the script from the VB help menu and yet am still having problems.

I am trying to run a loop that goes to a folder designated by user (not in sample code below as changed to try and get code to work) it then creates a list of the files contained in the folder. It then opens them one at a time. I then call and run another macro to crunch the data in the file (not shown in sample code attached). The macro then saves the file into another folder.

The problem I am having is that I have got the loop to work when signed into the eMac as the main admin user (first user account set up). However, If I sign into my own account (admin security level) and try and run the script on a folder on my desktop the script runs but then tells me that "The search will return too many files to display" or just crashes excel.

The only thing I can think of is perhaps there is some security issue - or file path problems? If I insert code to say open file with a path in the folder I have specified it opens no problem.

Any ideas how I can solve this problem?

Perhaps a complete rethink is needed and a different means of opening the files in the specified folder may be the answer?

Any ideas/help/suggestions/valium greatly appreciated.


Cheers, Christian.

Sub macro7()

'NOTE MY COMMENTS THAT START WITH 'AND ARE IN GREEN.
'DO NOT DO ANYTHING EXCEPT EXPLAIN WHATS HAPPENING.

'BLUE TEXT are built in VB commands.
'BLACK TEXT are instructions.
Dim StrInputFolder As String 'Defines loacation as a string (list of characters)
Dim StrOuputFile As String ' Defines loacation as a string (list of characters)

With Application.FileFind 'application built into MAC VB that lists all files
'in specified location (i.e. folder).
StrOuputFile = "Macintosh HD:Users:soundalbDesktop folder:Dummy"
'This is the location of the output file
.SearchPath = "Macintosh HD:Users:Shared:VB TEST DATA:"
'This is the location where the folder containing the files you want to process is.

.Options = msoOptionsNew ' Starts a new search (doesnot add files found to an existing serch)
.FileName = "" ' this is set to look for only excel files. leave as "" to look at all files
.Execute ' Do this.

StrInputFolder = .SearchPath 'Attributes StrInputFolder with characters - Search Patch above.

With .FoundFiles ' Using the results of the serach for files


If .Count > 0 Then ' if a file found (number of files (count) > 0) then


Dim Msg, Style, Title, Response, Answer ' set parameters for dialogue box
Msg = "Looked In:" & Chr(13) & Chr(13) & StrInputFolder & Chr(13) & Chr(13) & "There were " & .Count & _
" file(s) found." & Chr(13) & Chr(13) & "Do you want to continue?"
Style = vbOKCancel
Response = MsgBox(Msg, Style, Title)

If Response = vbOK Then 'if press OK then carry on

For i = 1 To .Count ' add one to the list of files found count
Workbooks.OpenText (.Item(i)) 'opens first file found (then number 2 etc.)



' calls sub called process. and passes StrOuputFile over to it.

Next i 'gets next file. ((i) plus one - adds one to file count.)

Else ' No files found (file found count = 0)
MsgBox "There were no files found." & Chr(13) & Chr(13) & "No Files have been ammended/altered."

End If ' stops the if statement.
End If 'stops the if statement.

End With ' stops the with statement.
End With ' stops the with statement.
End Sub ' Ends sub routine - stops the process