GetAttr("File.Name") Problem

J

JohnLee

Registered User.
Local time
Today, 07:18
Joined
Mar 8, 2007
Messages
692
Good day,

I'm hoping someone can help me here, I two sets of code which are almost identicle, one works fine but I can't work out why the other one doesn't. The code structure for both sets of code are the same with some differences with regards to where they get their information from and where they put and rename the images, please see below
Code: 
[FONT=Times New Roman]Function SkyQuesImages()[/FONT]
[FONT=Times New Roman]On Error Resume Next[/FONT]
[FONT=Times New Roman]'-------------------------------------------[/FONT]
[FONT=Times New Roman]'References:[/FONT]
[FONT=Times New Roman]'Visual Basic For Applications[/FONT]
[FONT=Times New Roman]'Microsoft Access 9.0 Object Library[/FONT]
[FONT=Times New Roman]'OLE Automation[/FONT]
[FONT=Times New Roman]'Microsoft ActiveX Data Objects 2.1 Library[/FONT]
[FONT=Times New Roman]'Microsoft Scripting Runtime[/FONT]
[FONT=Times New Roman]'Microsoft DAO 3.6 Object Library[/FONT]
[FONT=Times New Roman]'-------------------------------------------[/FONT]
[FONT=Times New Roman]    Dim DB As Database                                                      [/FONT]
[FONT=Times New Roman]    Dim File As File                                                        [/FONT]
[FONT=Times New Roman]    Dim FileLoc As String                                                   [/FONT]
[FONT=Times New Roman]    Dim Folder As Folder                                                    [/FONT]
[FONT=Times New Roman]    Dim FormCode As String                                                  [/FONT]
[FONT=Times New Roman]    Dim FS As FileSystemObject                                              [/FONT]
[FONT=Times New Roman]    Dim ImagePath As String                                                 [/FONT]
[FONT=Times New Roman]    Dim NameOfFile                                                          [/FONT]
[FONT=Times New Roman]    Dim Agent As String                                                     [/FONT]
[FONT=Times New Roman]    Dim SchemeCode As String                                                [/FONT]
[FONT=Times New Roman]    Dim PlanNumber As String                                                [/FONT]
[FONT=Times New Roman]    Dim SchRef As String                                                    [/FONT]
[FONT=Times New Roman]    Dim rst As DAO.Recordset                                                [/FONT]
[FONT=Times New Roman]    Dim SkyTable                                                            [/FONT]
[FONT=Times New Roman]    Dim strTemp As String                                                   [/FONT]
[FONT=Times New Roman]    Dim TifFilePath As String                                               [/FONT]
[FONT=Times New Roman]    Dim TopFolder As String                                                 [/FONT]
[FONT=Times New Roman]    [/FONT]
[FONT=Times New Roman]    Set DB = CurrentDb                                                      [/FONT]
[FONT=Times New Roman]    Set FS = CreateObject("Scripting.FileSystemObject")                     [/FONT]
[FONT=Times New Roman]    Set rst = DB.OpenRecordset("tblSky")                                    [/FONT]
[FONT=Times New Roman]    [/FONT]
[FONT=Times New Roman]    SkyTable = DCount("*", "tblSky")                                        [/FONT]
[FONT=Times New Roman]    [/FONT]
[FONT=Times New Roman]    If SkyTable > 0 Then                                                    [/FONT]
[FONT=Times New Roman]        Do While Not rst.EOF                                                [/FONT]
[FONT=Times New Roman]            If Not rst.EOF Then                                             [/FONT]
[FONT=Times New Roman]                FormCode = rst![strFormType]                                [/FONT]
[FONT=Times New Roman]                Agent = rst![strAgentNo]                                    [/FONT]
[FONT=Times New Roman]                SchemeCode = rst![strSchemeCode]                            [/FONT]
[FONT=Times New Roman]                PlanNumber = rst![strPlanNumber]                            [/FONT]
[FONT=Times New Roman]                SchRef = Agent & "_" & SchemeCode & PlanNumber              [/FONT]
[FONT=Times New Roman]                ImagePath = rst![strImagePath]                              [/FONT]
[FONT=Times New Roman]                TopFolder = Mid(ImagePath, 4, 25)                           [/FONT]
[FONT=Times New Roman]                TifFilePath = "J:\" & TopFolder                             [/FONT]
[FONT=Times New Roman]                [/FONT]
[FONT=Times New Roman]                If Not FS.FolderExists(TifFilePath) Then                    [/FONT]
[FONT=Times New Roman]                    MsgBox "Folder Doesn't Exist", , "Reading Tif Files"    [/FONT]
[FONT=Times New Roman]                    End                                                     [/FONT]
[FONT=Times New Roman]                End If[/FONT]
[FONT=Times New Roman]                [/FONT]
[FONT=Times New Roman]                Set Folder = FS.GetFolder(TifFilePath)                      [/FONT]
[FONT=Times New Roman]                For Each File In Folder.Files                               [/FONT]
[FONT=Times New Roman]                    NameOfFile = GetAttr("File.Name")                       [/FONT]
[FONT=Times New Roman]                    strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1) [/FONT]
[FONT=Times New Roman]                    FileLoc = Folder & "\" & File.Name                      [/FONT]
[FONT=Times New Roman]                    If FileLoc = ImagePath Then                             [/FONT]
[FONT=Times New Roman]                        FS.CopyFile Folder & "\" & strTemp, "M:\Customer Satisfaction\StdDGImages" & "\" & FormCode & "\" & SchRef & ".tif"     [/FONT]
[FONT=Times New Roman]                    End If[/FONT]
[FONT=Times New Roman]                    [/FONT]
[FONT=Times New Roman]                    DoEvents                                                [/FONT]
[FONT=Times New Roman]                    DoCmd.Echo True, "Copying File to New Loc: " & Folder & "\" & strTemp & "To: " & "M:\Customer Satisfaction\StdDGImages" & "\" & FormCode & "\" & SchRef & ".tif"    [/FONT]
[FONT=Times New Roman]                Next                                                        [/FONT]
[FONT=Times New Roman]            End If[/FONT]
[FONT=Times New Roman]            rst.MoveNext                                                    [/FONT]
[FONT=Times New Roman]        Loop[/FONT]
[FONT=Times New Roman]    End If[/FONT]
[FONT=Times New Roman]    [/FONT]
[FONT=Times New Roman]    SkyTable = 0                                                            [/FONT]
[FONT=Times New Roman]    rst.Close                                                               [/FONT]
[FONT=Times New Roman]    Set rst = Nothing                                                       [/FONT]
[FONT=Times New Roman]    Set DB = Nothing                                                        [/FONT]
[FONT=Times New Roman]    [/FONT]
[FONT=Times New Roman]    DoCmd.Echo True, "Standard DG 2012 Images Organising Process Complete"[/FONT]
[FONT=Times New Roman]    [/FONT]
[FONT=Times New Roman]End Function[/FONT]

The above code works fine every single time


Code: 
[FONT=Times New Roman][SIZE=3]Function BiasiImages()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]On Error Resume Next[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'-------------------------------------------[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'References:[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'Visual Basic For Applications[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'Mircrosoft Access 9.0 Object Library[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'OLE Automation[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'Microsoft ActiveX Data Objects 2.1 Library[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'Microsoft Scripting Runtime[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'Microsoft DAO 3.6 Object Library[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'-------------------------------------------[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim rst As DAO.Recordset[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim DB As Database[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim FS As FileSystemObject[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim Folder As Folder[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim File As File[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim TifFilePath As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim NameOfFile As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim strTemp As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim FileLoc As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim TopFolder As String[/SIZE][/FONT]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim Title As String[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim Initials As String[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim Surname As String[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim Customer As String[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim VerifiedDate As String[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim ImageLoc As String[/FONT][/SIZE][/COLOR][/B]
[B][COLOR=blue][SIZE=3][FONT=Times New Roman]Dim Biasi70854Query As String[/FONT][/SIZE][/COLOR][/B]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set DB = CurrentDb[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set FS = CreateObject("Scripting.FileSystemObject")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set rst = DB.OpenRecordset("[B][COLOR=blue]qrytblBiasi70854Process_01Images[/COLOR][/B]")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][B][COLOR=blue]Biasi70854Query[/COLOR][/B] = DCount("*", "[B][COLOR=blue]qrytblBiasi70854Process_01Images[/COLOR][/B]")[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][B][COLOR=blue]If Biasi70854Query[/COLOR][/B] > 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Do While Not rst.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        If Not rst.EOF Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Title = rst![strTitle][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Initials = rst![strInitials][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Surname = rst![strSurname][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            VerifiedDate = rst![CurDate][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Customer = Title & " " & Initials & " " & Surname & "_" & VerifiedDate[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            ImageLoc = rst![ImagePath][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            TopFolder = Mid(ImageLoc, 4, 27)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            TifFilePath = "J:\" & TopFolder[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            If Not FS.FolderExists(TifFilePath) Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                MsgBox "Folder Doesn't Exist", , "Reading Tif Files"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                End[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            End If[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            Set Folder = FS.GetFolder(TifFilePath)[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            For Each File In Folder.Files[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                [B][COLOR=red]NameOfFile = GetAttr("File.Name")[/COLOR][/B][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                FileLoc = Folder & "\" & File.Name[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]                [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]                If FileLoc = ImageLoc Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                    FS.CopyFile Folder & "\" & strTemp, "[B][COLOR=blue]H:\BiasiImages" & "\" & Customer[/COLOR][/B] & ".tif"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                DoEvents[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                DoCmd.Echo True, "Copying File to New Loc: " & Folder & "\" & strTemp & "To :" & "[B][COLOR=blue]H:\BaisiImages" & "\" & Customer [/COLOR][/B]& ".tif"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Next[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Loop[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End If[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman][B][COLOR=blue]BiasiQuery[/COLOR][/B] = 0[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]rst.Close[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set rst = Nothing[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set DB = Nothing[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]DoCmd.Echo True, "Biais 70854 Process Complete"[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]                    [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End Function[/SIZE][/FONT]

The line in red is where this code keeps falling over, it's saying that it can't find the tif files although the files do exist, I've checked and double checked, I just stuck on trying to work out why this is, the only differences between this code structure and the first one above are shown in blue in this bit of code, so my thoughts are that this should work just like the other code, but not so far.

Hopefully someone can spot what may be wrong with the second code block.

Assistance/pointers would be most gratefully accepted.

Regards

John
 
I don't see how this line works [I haven't tested the apporahc so could be off base]
NameOfFile = GetAttr("File.Name")

If you put quotes around the actual file name eg "MyWeeklyOrders.txt" I could see it.
But I think it's looking for a file called File.Name

See this M$oft reference
http://msdn.microsoft.com/en-us/library/hds568h4(v=vs.80).aspx


EDIT:
I have tested the GetAttr("File.Name") in your code. It does not return a value, nor does it give an error. I even tried
GetAttr(file.name)
You'll see in attached jpg tha NameOfFile is Empty even though I have processed the GetAttr statement.
 

Attachments

  • GetAttr_NameOfFile.jpg
    GetAttr_NameOfFile.jpg
    55.9 KB · Views: 105
Last edited:
Hi Thanks for your response,

That's just it, in my first code block I have quotes around the File.Name and it works fine, in my second code block I did the same and I also removed the quotes GetAttr (File.Name) and when it runs the same problem occurs it's complaining that it can't find the file. The problem is that in my first code block I do have GetAttr("File.Name") and it works for that code block and I have several other code blocks that have the same structure but for different data and they work!, really really strangem that it doesn't work in this one, I've even tried it by putting it in its own Database on it's own and it just doesn't work, the folders and files do exist. What it should be doing is looking in the folder J:\20120123\00235331_BIASI_001 and then checking to find the tif file associated with the data in the query so for instance in line one the image associated with that data is 00235331_BIASI_001_2.tif and on finding that file it then copies it to my BiasiImages folder and renames the tif file with the customer information as specified in my code block. I guess I'll have to rework the code to see if I can what I want. Thanks for your response.

John
 
I just updated my last post while you were responding.

Check your NameOfFile right after the GetAttr line and see if you have a value in NameOfFile.
 
Hi,

Yes your right it doesn't I even checked my code blocks that work and they don't return any values yet they fine the tif files, copy and rename them to the desired location, which is strange indeed. I commented out that line of code and tried it, it just goes in a forever loop. very strange, I'll keep on working at it and hopefully I'll find what it is.

Thanks

John
 
Of course it would appear to work when you have On Error Resume Next as your error handler.
 
Hi Yes,

your right it does hide any errors like this one that might accur, I'll need to come up with a better way of dealing with that aspect. I have now got to the bottom of the problem and that slice of code actually doesn't do anything even in my other code blocks and because I had poor error handling i.e. the On Error Resume Next I wasn't aware of that. So lesson learnt and all code amended accordingly.

Thanks for you observartions and comments

John
 
John,

If you do not have MZTools - a free extremely useful utility -get it.
You can insert some formatting and Error Handling on the click of an option.

http://www.MZTools.com

Good luck.
 
Hi Jdraw,

Thanks for the tip, I pay that link a visit.

Cheers

John
 
You must log in or register to reply here.

Similar threads

MsAccessNL
Outlook import csv error
Replies
5
Views
392
MsAccessNL
MsAccessNL
T
Problem with NAME Statement
Replies
2
Views
197
Mark_
M
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
1,000
The_Doc_Man
The_Doc_Man
P
Extract Zip
Replies
6
Views
496
Eugene-LS
Eugene-LS
P
Solved Access VBA
Replies
4
Views
554
prasadgov
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom