Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
|
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 13th, 2004, 10:25 AM
Registered User
 
Join Date: May 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dir function and looping through all files

i have a problem with error message saying:
invalid procedure call or argument
what i'm i doing wrong as had this working fine loopng through untill added some more logic
now doesnt loop
the error is on the calling of the next file id directory see line marked ***********************
thanks ...appreciate help, need it urgently.
---------------------------

Sub Macro1()
'
' Macro1 Macro
'
'

     'Messages
Dim messgeNoIAU As String
Dim messgeNolistNub As String
Dim messgeSuccess As String
Dim IUID As String
Dim logFile As String
Dim NotdoneDupTest As Boolean

Dim messgeDupliID As String
Dim dupFlag As Boolean
Dim passingfileName As String


messgeDupliID = "Practise ID is duplicated"
messgeNoIAU = "had No Practise ID"
messgeNolistNub = "had No list number"
messgeSuccess = "was sucessfully processed"

    'go through all files
    If FileFilter = "" Then FileFilter = "*.xls"
    TargetFolder = "D:\Projects\SNE\IU's\extractedXmlFiles0600504\mar chpst\Inbox\"
    fn = Dir(TargetFolder & FileFilter) ' the first file name in the folder
    While Len(fn) > 0





    dupFlag = False
    NotdoneDupTest = True
    practiseID = False
    practiceListSize = False
    Workbooks.Open fileName:= _
        TargetFolder & fn

    'GotoFirstSheet
    Call GotoFirstSheet

    Range("B26").Select
    IUID = ActiveCell.Value


    If IsEmpty(ActiveCell) Then
        practiseID = False
    Else
        practiseID = True
    End If


     Range("B27").Select
    If IsEmpty(ActiveCell) Then

        practiceListSize = False
    Else
        practiceListSize = True
    End If

    If (practiseID = False) Then
    ActiveWorkbook.SaveAs fileName:= _
        "D:\Projects\SNE\IU's\extractedXmlFiles0600504\mar chpst\PractiseIDFailed\" & fn _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

        logFile = "D:\Projects\SNE\IU's\naeemsFailLogFile.xls"
        Call ManipulateFileName(ActiveWorkbook.Name, messgeNoIAU, logFile, IUID)

    ElseIf (practiceListSize = False) Then
    ActiveWorkbook.SaveAs fileName:= _
        "D:\Projects\SNE\IU's\extractedXmlFiles0600504\mar chpst\practiceListSizeFailed\" & fn _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

       logFile = "D:\Projects\SNE\IU's\naeemsFailLogFile.xls"
       Call ManipulateFileName(ActiveWorkbook.Name, messgeNolistNub, logFile, IUID)


    ElseIf NotdoneDupTest Then
    dupFlag = CheckPracIDDup(ActiveWorkbook.FullName, IUID)
        If dupFlag Then
        ActiveWorkbook.SaveAs fileName:= _
            "D:\Projects\SNE\IU's\extractedXmlFiles0600504\mar chpst\dupicateIDFailiure\" & ActiveWorkbook.Name _
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False

        logFile = "D:\Projects\SNE\IU's\naeemsFailLogFile.xls"
        Call ManipulateFileName(ActiveWorkbook.Name, messgeDupliID, logFile, IUID)
        NotdoneDupTest = False



' do duplicate test

'first write all IUIDs to a file (temp checking file) ie every time u get here
'this writing will be IAU ID to file + name of file
' then check if current IAU ID already exist in list if does
'get name of file and open it saveit in Duplicate folder
'delete it from all validation passed section
'place current file also in duplicate fail folder
' log the IAU and file details in duplicate log file
' open log file of successful and search for math of IAU in 4th coloubm
' copy entire row into duplicate log file
' delete entire row from allvalidation passed








  Else
    ActiveWorkbook.SaveAs fileName:= _
        "D:\Projects\SNE\IU's\extractedXmlFiles0600504\mar chpst\allValidationPassed\" & fn _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

    logFile = "D:\Projects\SNE\IU's\naeemscompleteLogFile.xl s"
    Call ManipulateFileName(ActiveWorkbook.Name, messgeSuccess, logFile, IUID)
    End If
 End If

        ActiveWorkbook.Close
       fn = Dir ' the next file name in the folder '************************
    Wend


End Sub

 
Old May 13th, 2004, 11:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 627
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear Poster,
I did not see any declaration for the variable fn in your code (must be a string) that shouldn't cause any problems, but if you miss 'Option Explicit' in your code the problem can be some other variables mismatch. I also noticed the length of the Marco1 method: in case of errors it makes more difficult to localize it. Consider splitting Macro1 into more methods and add error catching in all of them.
Another suggestion is always to run with full compile (Ctrl+F5) to catch any compiler errors in advance.

Let us know how it goes.
Marco
 
Old May 13th, 2004, 12:01 PM
Registered User
 
Join Date: May 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

how do i use the option explicit and i have been using fn as a variant through out its worked fine but i will try as string
the macro1 calls other functions and sub routines , but basically the code is quite lenthy i havent been able to debug the actual beast sub funtion of selecting an id from cell checking if it exist in previuosly processed ids but there could be 10000 of them so i cant do it as an array can i ?
ie go through and serch if there is duplicate get its file path and open it and saveas different file path etc....
i have added this type of code in to place values in excell and the loop through trying to match .....since adding this duplicate checking code thisngs have broken , has any one ever done anything like that , ie checking for duplicate values whilst processing excel file?
how do i catch exceptions in VBA?
thanks






Similar Threads
Thread Thread Starter Forum Replies Last Post
Cant open WroxUnited Virtual Dir bcr212 ASP.NET 2.0 Basics 3 May 11th, 2006 05:48 AM
Is there a Dir() Function with JavaScript rguidry Javascript How-To 2 October 3rd, 2005 11:54 AM
Setting up Virtual Dir in IIS dsuratman ASP.NET 1.0 and 1.1 Basics 4 June 20th, 2005 07:57 AM
get a collection of filenames/paths from a dir? oowllew C# 0 August 13th, 2004 03:52 PM
Looping through variables, Applying a function tdaustin Classic ASP Basics 0 August 3rd, 2003 07:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.