Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: finding the real file name behind the variable rather than a string

    Gurus,

    I wrote a vba sub and define a variable to retrieve the file name from table. Within the sub, I write a loop to get the file names using a string to concatenate the looping increment variable to form the real file names ftxtrefc1, ftxtrefc2, ftxtrefc3, etc and then convert the text files to excel files by calling the function ProcessTextFile11(filename, delim1, texcel) in the loop. However, when calling the function, it errors out due to “file not found” error. The error highlight on the line

    Open TextFileName For Input As #intHandle

    in the function. The TextFileName display “filename” as the file name in the debug mode.

    I use msgbox try to find out what the real file name is in the subroutine.

    MsgBox ("reference file name filename=" & filename) pop out the message box :

    "reference file name filename= ftxtrefc1"

    MsgBox ("refrerence file name ftxtrefc1=" & ftxtrefc1) pop out the message box:

    "refrerence file name ftxtrefc1= c:\temp\test.txt"


    I think the program does not translate filename= ftxtrefc1 to the real file name - c:\temp\test.txt behind the variable ftxtrefc1. Rather, it translates ftxtrefc1 as string.

    I will be grateful if you can help me to resolve this problem to make the program recognize that
    filename as the real file name (= c:\temp\test.txt.) rather than a string

    Thank you very much


    Private sub button1 ( )

    Dim ftxtrefc1, ftxtrefc2, ftxtrefc3, ftxtrefc4, ftxtrefc5, ftxtrefc6, ftxtrefc7, ftxtrefc8 As Variant
    Dim refctno1 as integer


    ftxtrefc1 = DLookup("[refc1]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')

    refctno1 = DLookup("[refctno]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')


    Dim filename As String

    Dim intRefno As Integer
    intRefno = 1

    Do While intRefno < refctno1 + 1

    filename = "ftxtrefc" & intRefno

    MsgBox ("reference file name filename=" & filename)

    MsgBox ("reference file name ftxtrefc1=" & ftxtrefc1)

    Call ProcessTextFile11(filename, delim1, texcel)

    intRefno = intRefno + 1

    Loop

    End Sub


    Public Function ProcessTextFile11(ByVal TextFileName As String, ByVal delim1 As String, ByVal ExcelFileName As String)

    …...............

    ….............................
    intHandle = FreeFile
    Open TextFileName For Input As #intHandle
    …............................................

    End Function

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does you code do when dlookup fails
    have you checked to see if the dlookups are valid

    it helps if you insert code using [ c o d e ] and [ / c o d e ] tags (remove the spaces)
    Code:
    Private sub button1 ( )
    Dim ftxtrefc1, ftxtrefc2, ftxtrefc3, ftxtrefc4, ftxtrefc5, ftxtrefc6, ftxtrefc7, ftxtrefc8 As Variant
    Dim refctno1 as integer
    ftxtrefc1 = DLookup("[refc1]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')
    refctno1 = DLookup("[refctno]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')
    Dim filename As String
    Dim intRefno As Integer
    intRefno = 1
    Do While intRefno < refctno1 + 1
      filename = "ftxtrefc" & intRefno
      MsgBox ("reference file name filename=" & filename)
      MsgBox ("reference file name ftxtrefc1=" & ftxtrefc1)
      Call ProcessTextFile11(filename, delim1, texcel)
      intRefno = intRefno + 1
    Loop
    End Sub
    
    Public Function ProcessTextFile11(ByVal TextFileName As String, ByVal delim1 As String, ByVal ExcelFileName As String)
    
    …...............
    
    ….............................
    intHandle = FreeFile
    Open TextFileName For Input As #intHandle
    …............................................
    
    End Function
    id put a break point on the dlookups
    step through the code and see how those dlookups work
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    Thanks so much for response, healdem.

    dlookup is outside the loop (before the loop). I define those file names as variants so it won't fail if null. The dlookup does work without any issue. I put Sub and End Sub, Function and End Function for codes. I am not smart enough to figure out how to do the debugging in access. It's really confusing. Sorry. That's the reason I use msgbox to debug. It errors out when call the function since the function can not find the file name since it translate the file name as string rather than the variable with the real file name returned from the dlookup.

    I hope you can understand what I do I mean....

    Thanks again.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Google

    i suspect your real problem is
    filename = "ftxtrefc" & intRefno
    as effectively you are appending a number to the filename that has an extension
    assume it starts at filename.ext
    on the first iteration that will become
    filename.ext1
    next
    filename.ext2
    next filename.ext3
    ..and so on

    if you develop your debugging skills and step through the code you can quickly understand what your code is doing. the debugger is one of the more powerfull tools in the Access armoury
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2007
    Posts
    88
    Thanks again for helping and also sending me the link for debugging.

    in this line : filename = "ftxtrefc" & intRefno the ftxtrefc is a regular string as I quote it and it does not have the number like ftxtrefc1, ftxtrefc2, etc, thus, it is not the file name returned from the dlookup and won't have extension. It won't have the problem like
    filename.ext1
    next
    filename.ext2
    next filename.ext3

    I am hoping "ftxtrefc" & intRefno can make the filename become ftxtrefc1, ftxtrefc2 when step into the loop. From the message box, it dose return correct file name

    MsgBox ("reference file name filename=" & filename) pop out the message box :

    "reference file name filename= ftxtrefc1"

    MsgBox ("refrerence file name ftxtrefc1=" & ftxtrefc1) pop out the message box:

    "refrerence file name ftxtrefc1= c:\temp\test.txt"

    I think the problem is the program does not recognize it's a file name variable, rather, the program think it's a string.

    I will be very grateful if you can help me to resolve this.

    Thanks again!

  6. #6
    Join Date
    Aug 2007
    Posts
    88
    I found a similar case online. Let's make it simple.

    Const Value1 as long = 5
    Const Value2 as long = 12
    Const Value3 as long = 7

    For i = 1 to 3
    MsgBox Value & i '<-- How to do this properly?
    Next i

    How to make MsgBox to display 5 then 12 then 7 rather than Value1 then Value2 then Value3?

    Thank you very much for help!

    Quote Originally Posted by healdem View Post
    Google

    i suspect your real problem is
    filename = "ftxtrefc" & intRefno
    as effectively you are appending a number to the filename that has an extension
    assume it starts at filename.ext
    on the first iteration that will become
    filename.ext1
    next
    filename.ext2
    next filename.ext3
    ..and so on

    if you develop your debugging skills and step through the code you can quickly understand what your code is doing. the debugger is one of the more powerfull tools in the Access armoury

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by newguyinaccess View Post
    How to make MsgBox to display 5 then 12 then 7 rather than Value1 then Value2 then Value3?
    You can't. The name of a constant is fixed at compile time (it is "hard-coded"). What you can do is:
    Code:
    Sub TestConst()
    
        Const Value1 As Long = 5
        Const Value2 As Long = 12
        Const Value3 As Long = 7
        
        Dim i As Long
        
        For i = 1 To 3
            MsgBox Choose(i, Value1, Value2, Value3)
        Next i
        
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Aug 2007
    Posts
    88
    Thanks so much, Sinndho.

    What if the looping variable maximum is also dynamic and the Value is not constant?

    If the looping maximum is dynamic based on the value the user entered in the text box in the form. We can not use choose function.

    My case is the file names and the looping max will affect how many files I need to process.

    Any insight?

    Thanks again!

    Quote Originally Posted by Sinndho View Post
    You can't. The name of a constant is fixed at compile time (it is "hard-coded"). What you can do is:
    Code:
    Sub TestConst()
    
        Const Value1 As Long = 5
        Const Value2 As Long = 12
        Const Value3 As Long = 7
        
        Dim i As Long
        
        For i = 1 To 3
            MsgBox Choose(i, Value1, Value2, Value3)
        Next i
        
    End Sub

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the "looping maximum" is dynamic, with your system it means that:

    1. There must be as many declared constants as the max value of the counter ("looping maximum").
    2. As constants cannot be dynamically declared or allocated, it implies that you must fix an absolute maximum value for the counter (a limit).
    3. The Choose function remains somewhat usable.

    This system becomes practically unusable if the limit is rather large (imagine having to declare 1000 constants!).

    Personally, I would change the way the system works in one of these ways:

    1. If its possible, I would compute the value that's now declared as a constant from the value of the counter.

    2. If the first solution is not possible, I would store all possible values into a local table and retrieve each value using a Recordset or a DLookUp function:

    a) Table (name: Tbl_Lookup)
    - Column 1: (name: LookupIndex, Type: Long, Primary key)
    - Column 2: (name LookupValue, Type: Long, Indexed duplicates OK)

    With your sample values, we have:
    Code:
                |
    LookupIndex | LookupValue
    ------------+------------
      1         |   5
      2         |  12
      3         |   7
    Then:

    b) Using a Recordset:
    Code:
    Sub TestLookup_Recordset()
    
        Dim rst As DAO.Recordset
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset("Tbl_Lookup", dbOpenSnapshot)
        For i = 1 To 3
            rst.FindFirst "LookupIndex=" & i
            If rst.NoMatch = False Then MsgBox rst!LookupValue
        Next i
        rst.Close
        Set rst = Nothing
        
    End Sub
    c) Using the function DLookup:
    Code:
    Sub TestLookup_DLookup()
    
        Dim i As Long
        Dim varValue As Variant
        
        For i = 1 To 3
           varValue = DLookup("LookupValue", "Tbl_Lookup", "LookupIndex=" & i)
           If Not IsNull(varValue) Then MsgBox varValue
        Next i
        
    End Sub
    However, except with the solution where the value can be computed from the loop counter, you'll need to fix an upper limit for the loop.
    Have a nice day!

  10. #10
    Join Date
    Aug 2007
    Posts
    88
    Thanks again, Sinndho.

    This is the subroutine. Actually the looping max is very limited (max will be 8 and the value will be depended on the value user input into a text box)

    ftxtrefc1 returned from dlookup is like c:\temp\test.txt

    MsgBox ("reference file name filename=" & filename) -- comes out
    reference file name filename= ftxtrefc1
    and it takes it as a string "ftxtrefc1" rather than the real file c:\temp\test.txt I want. Any way to resolve it?

    Since the max is only up to 8, I can use select case to resolve it rather than looping, but it is not a good coding practice and not neat.

    Any insight will be greatly appreciated!




    Private sub button1 ( )

    Dim ftxtrefc1, ftxtrefc2, ftxtrefc3, ftxtrefc4, ftxtrefc5, ftxtrefc6, ftxtrefc7, ftxtrefc8 As Variant
    Dim refctno1 as integer


    ftxtrefc1 = DLookup("[refc1]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')

    ftxtrefc2 = DLookup("[refc2]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')

    refctno1 = DLookup("[refctno]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')


    Dim filename As String

    Dim intRefno As Integer
    intRefno = 1

    Do While intRefno < refctno1 + 1

    filename = "ftxtrefc" & intRefno

    MsgBox ("reference file name filename=" & filename)

    MsgBox ("reference file name ftxtrefc1=" & ftxtrefc1)

    Call ProcessTextFile11(filename, delim1, texcel)

    intRefno = intRefno + 1

    Loop

    End Sub

    Quote Originally Posted by Sinndho View Post
    If the "looping maximum" is dynamic, with your system it means that:

    1. There must be as many declared constants as the max value of the counter ("looping maximum").
    2. As constants cannot be dynamically declared or allocated, it implies that you must fix an absolute maximum value for the counter (a limit).
    3. The Choose function remains somewhat usable.

    This system becomes practically unusable if the limit is rather large (imagine having to declare 1000 constants!).

    Personally, I would change the way the system works in one of these ways:

    1. If its possible, I would compute the value that's now declared as a constant from the value of the counter.

    2. If the first solution is not possible, I would store all possible values into a local table and retrieve each value using a Recordset or a DLookUp function:

    a) Table (name: Tbl_Lookup)
    - Column 1: (name: LookupIndex, Type: Long, Primary key)
    - Column 2: (name LookupValue, Type: Long, Indexed duplicates OK)

    With your sample values, we have:
    Code:
                |
    LookupIndex | LookupValue
    ------------+------------
      1         |   5
      2         |  12
      3         |   7
    Then:

    b) Using a Recordset:
    Code:
    Sub TestLookup_Recordset()
    
        Dim rst As DAO.Recordset
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset("Tbl_Lookup", dbOpenSnapshot)
        For i = 1 To 3
            rst.FindFirst "LookupIndex=" & i
            If rst.NoMatch = False Then MsgBox rst!LookupValue
        Next i
        rst.Close
        Set rst = Nothing
        
    End Sub
    c) Using the function DLookup:
    Code:
    Sub TestLookup_DLookup()
    
        Dim i As Long
        Dim varValue As Variant
        
        For i = 1 To 3
           varValue = DLookup("LookupValue", "Tbl_Lookup", "LookupIndex=" & i)
           If Not IsNull(varValue) Then MsgBox varValue
        Next i
        
    End Sub
    However, except with the solution where the value can be computed from the loop counter, you'll need to fix an upper limit for the loop.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by newguyinaccess View Post
    Private sub button1 ( )

    Dim ftxtrefc1, ftxtrefc2, ftxtrefc3, ftxtrefc4, ftxtrefc5, ftxtrefc6, ftxtrefc7, ftxtrefc8 As Variant
    Dim refctno1 as integer


    ftxtrefc1 = DLookup("[refc1]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')

    ftxtrefc2 = DLookup("[refc2]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')

    refctno1 = DLookup("[refctno]", "referenceinfo", "[projectno] = '" & projectno & "' And [company] = '" & company & "' And [test] = '" & Test & "' And [extension] = '" & extension & "')


    Dim filename As String

    Dim intRefno As Integer
    intRefno = 1

    Do While intRefno < refctno1 + 1

    filename = "ftxtrefc" & intRefno

    MsgBox ("reference file name filename=" & filename)

    MsgBox ("reference file name ftxtrefc1=" & ftxtrefc1)

    Call ProcessTextFile11(filename, delim1, texcel)

    intRefno = intRefno + 1

    Loop

    End Sub
    Maybe I'm dumb or missing something entirely but why don't you use:
    Code:
    Private Sub button1()
    
        Const c_Criteria As String = "projectno = '@P' And company = '@C' And test = '@T' And extension = '@E'"
        
        Dim filename As String
        Dim strCriteria As String
        Dim intRefno As Integer
        Dim refctno1 As Integer
    
        strCriteria = Replace(Replace(Replace(Replace(c_Criteria, "@P", projectno), _
                                                                  "@C", company), _
                                                                  "@T", test), _
                                                                  "@E", Extension)
        refctno1 = DLookup("[refctno]", "referenceinfo", strCriteria)
        For intRefno = 1 To refctno1
            filename = DLookup("refc" & intRefno, "referenceinfo", strCriteria)
            MsgBox ("reference file name filename=" & filename)
            Call ProcessTextFile11(filename, delim1, texcel)
        Next intRefno
    
    End Sub
    Have a nice day!

  12. #12
    Join Date
    Aug 2007
    Posts
    88
    Thanks so much, Sinndho.

    You suggested a very interesting way to do it. I will try to use this method in my program. Will keep you post.

    Thanks again!

    Quote Originally Posted by Sinndho View Post
    Maybe I'm dumb or missing something entirely but why don't you use:
    Code:
    Private Sub button1()
    
        Const c_Criteria As String = "projectno = '@P' And company = '@C' And test = '@T' And extension = '@E'"
        
        Dim filename As String
        Dim strCriteria As String
        Dim intRefno As Integer
        Dim refctno1 As Integer
    
        strCriteria = Replace(Replace(Replace(Replace(c_Criteria, "@P", projectno), _
                                                                  "@C", company), _
                                                                  "@T", test), _
                                                                  "@E", Extension)
        refctno1 = DLookup("[refctno]", "referenceinfo", strCriteria)
        For intRefno = 1 To refctno1
            filename = DLookup("refc" & intRefno, "referenceinfo", strCriteria)
            MsgBox ("reference file name filename=" & filename)
            Call ProcessTextFile11(filename, delim1, texcel)
        Next intRefno
    
    End Sub

  13. #13
    Join Date
    Aug 2007
    Posts
    88
    I read your code very carefully and I believe it should work. However, maybe I miss something when use it in my program, the result is very weird and does not make sense. I don't even know why it is like that.

    reference file name filename= 1

    reference file name filename= 2

    reference file name filename= 3

    It seems like the lookup function ignore the string "refc" but only select the number.

    Thanks again.

    Quote Originally Posted by Sinndho View Post
    Maybe I'm dumb or missing something entirely but why don't you use:
    Code:
    Private Sub button1()
    
        Const c_Criteria As String = "projectno = '@P' And company = '@C' And test = '@T' And extension = '@E'"
        
        Dim filename As String
        Dim strCriteria As String
        Dim intRefno As Integer
        Dim refctno1 As Integer
    
        strCriteria = Replace(Replace(Replace(Replace(c_Criteria, "@P", projectno), _
                                                                  "@C", company), _
                                                                  "@T", test), _
                                                                  "@E", Extension)
        refctno1 = DLookup("[refctno]", "referenceinfo", strCriteria)
        For intRefno = 1 To refctno1
            filename = DLookup("refc" & intRefno, "referenceinfo", strCriteria)
            MsgBox ("reference file name filename=" & filename)
            Call ProcessTextFile11(filename, delim1, texcel)
        Next intRefno
    
    End Sub

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    filename is what's retrieved from the column refcX (with X going from 1 to refctno1: refc1, refc2, ...) of the table referenceinfo for the row matching the criteria:
    Code:
    filename = DLookup("refc" & intRefno, "referenceinfo", strCriteria)
    If the contents of this column is numeric and if you want to prefix the retrieved number with the constant string "refc", you can use:
    Code:
    filename = "refc" & DLookup("refc" & intRefno, "referenceinfo", strCriteria)
    Have a nice day!

  15. #15
    Join Date
    Aug 2007
    Posts
    88
    The column in the table is an actual file name rather than numeric. It's very odd that the dlookup return 1, 2, 3, etc, completely ignore the string refc, and did not return the actual file name.

    If I use filename = "refc" & DLookup("refc" & intRefno, "referenceinfo", strCriteria), it return a string refc1, refc2, refc3, etc rather than the real file name.

    Thanks again.

    Quote Originally Posted by Sinndho View Post
    filename is what's retrieved from the column refcX (with X going from 1 to refctno1: refc1, refc2, ...) of the table referenceinfo for the row matching the criteria:
    Code:
    filename = DLookup("refc" & intRefno, "referenceinfo", strCriteria)
    If the contents of this column is numeric and if you want to prefix the retrieved number with the constant string "refc", you can use:
    Code:
    filename = "refc" & DLookup("refc" & intRefno, "referenceinfo", strCriteria)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •