UtterAccess Discussion Forums > Using File System Object With Folder

Full Version: Using File System Object With Folder

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

ipisors

Apr 9 2012, 06:35 PM

This exact type of code (far as I can tell) works for me in another situation. I can't figure out why I get "type mismatch" on the line that has GetFolder

CODE

Dim strMisMatch As String 'the sub-category of error, MU: text does not match file name.
Dim strMisMatch2 As String 'the sub-category of error, inner text-DATE does not match file name
Dim strDate1 As String
Dim strDate2 As String
Dim myFSO As New FileSystemObject
Dim myFolder As Folder
Dim myTextFile As TextStream
Dim oFile As File
Dim strTextLine As String
Dim intLine As Integer

Set myFolder = myFSO.GetFolder("\\Server\Folder\Folder\Folder\Folder\Folder\Folder\Folder")

norie

Apr 9 2012, 07:59 PM

Isaac

I'm not sure FSO works in the same way with network folders/drives (or UNC paths) as it does with local folders/drives.

I think in some situations you need to supply the username/password.

ipisors

Apr 10 2012, 01:40 PM

Well, I'm pretty stumped here. According to microsoft, GetFolder as an FSO method works with Absolute or Relative paths. And the thing is, I swear...I have a program out there that several users use every single day that uses this exact code...the ONLY thing I changed for the purpose of this post was the actual server, after the \\. the rest is even the same:

CODE

   Dim FS As New FileSystemObject
    Dim oFolder As Folder
    Dim oFile As File
    Dim ofolderDestin As String

    Set oFolder = FS.GetFolder("\\SERVER\AllAFG_5602\DOCUMENT MANAGEMENT\Analyst Shared Files\Imaging\COLL-Warner Collections\TotalLoss\AllImaged\" & ThisWorkbook.Worksheets("Report").Range("J1").Value)
    ofolderDestin = ("\\SERVER\data\5602\CRR\Production\CRR Image\NOITS Rejects")
    For Each oFile In oFolder.Files
   If InStr(Left(oFile.Name, 16), ".") Or InStr(oFile.Name, "[") Or InStr(oFile.Name, "]") Or InStr(oFile.Name, "pdf.pdf") Or InStr(oFile.Name, "doc.doc") Or InStr(oFile.Name, " ") Or InStr(oFile.Name, "?") Or Len(oFile.Name) < 21 Or InStr(oFile.Name, "-") Or Len(oFile.Name) > 26 Or Right(oFile.Name, 3) = "tmp" Or InStr(oFile.Name, "~") Or InStr(oFile.Name, "900.") Or Left(oFile.Name, 2) <> "50" Or InStr(oFile.Name, "!") Then
       ThisWorkbook.Worksheets("Report").Range("A30").Value = ThisWorkbook.Worksheets("Report").Range("A30").Value + 1
       ThisWorkbook.Worksheets("Report").Range("H65536").End(xlUp).Offset(1, 0).Value = oFile.Name
       oFile.Move (ofolderDestin & "\" & Format(Now, "mmddyy-hhmm.") & oFile.Name)

      End If
    Next oFile

Secondly about the authorization, this server I"m already authenticated on it. If paste that absolute path in an explorer window and hit enter, it comes right up. I can use Dir() to test for file existence in that folder....I can Kill() files in that folder, all with no problem and no authentication.

Lastly, I just tested the Absolute vs. Relative possibility by mapping it to Z, and this gives me the exact same "Type mismatch" error.....What could possibly be a type mismatch about this - string argument???? I am so stumped.

CODE

Dim strMisMatch As String 'the sub-category of error, MU: text does not match file name.
Dim strMisMatch2 As String 'the sub-category of error, inner text-DATE does not match file name
Dim strDate1 As String
Dim strDate2 As String
Dim myFSO As New FileSystemObject
Dim myFolder As Folder
Dim myTextFile As TextStream
Dim oFile As File
Dim strTextLine As String
Dim intLine As Integer

Set myFolder = myFSO.GetFolder("Z:\Shared\CallCntrAnalyst\Report Analyst\CRC KPIs\Database\Source Files\IEX")

And where the heck did the insert special item-Codebox go in my UA reply?

ipisors

Apr 10 2012, 01:43 PM

Ok, after reading this article, I changed

CODE

Dim myFolder As Folder

CODE

Dim myFolder As Scripting.Folder

and it worked. I guess I should have been more specific.
wonder why it works in one workbook and not in another...

but I am all set I guess on this one

norie

Apr 10 2012, 02:30 PM

Isaac

Good spot, when I reread your first post and read post #3 I was going to suggest declaring it as Object.

Then I scrolled down and saw post #4.

Decalring as Object would have worked but it makes far more sense to declare it properly.

ipisors

Apr 10 2012, 02:33 PM

I'm pretty sure I'm using the wrong terminology here, but I've started to make up a rule that goes something like this:

if you're running vba in (for example, Excel), and you want to use something that's not "native" to Excel (like worksheet, workbook, range, cell), then "always" prefix it with the library type.

Like, Dim myDb as DAO.Database
or, Dim myDoc as Word.Document

Of if I'm using Access VBA, I always use:
Dim myWb as Excel.Workbook
Dim myWs as Excel.Worksheet
... And then continue on to use the Excel vba I'm more familiar with.

or this example.

Is that a good rule?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.