Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2008
    Posts
    7

    Unanswered: How to display text from .txt file or Access through message box?

    Hi all.

    I am using Excel VBA 2007 to create a message box, and I'm new to VBA programming But I have managed to create a simple message box with those Excel VBA books that i have borrowed. However, I need to further improve on the codes!

    The eg of simple codes:

    Code:
    Sub ShowMessage()
        
        MsgBox ("ARFCN : 79 (E-GSM)" _
        & vbCr & "L2 Pseudo Length : 18" _
        & vbCr & "Skip indicator : 0" _
        & vbCr & "Protocol discriminator : (6) Radio resources management messages" _
        & vbCr & "Message type : 27")
    End Sub
    Say the user needs to double-click on a certain cell in the Excel worksheet, after which a message box will popped out to show necessary details for that particular cell. So, how do I display a lump sum of text from a .txt file or from database Access in a message box after double-clicking a cell? Anybody has got any idea?
    Last edited by blackyroses; 09-25-08 at 03:37.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You have to read the text file... Have a look at the FileSystemObject and/or TextStream in help files.

    Here's something from my bookmarks that will be useful; http://www.xtremevbtalk.com/showthread.php?t=123814
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2008
    Posts
    7
    Hi George, thanks for the link! I will take a look at it

  4. #4
    Join Date
    Sep 2008
    Posts
    7
    Hi George, to be honest, it's more than a little over my head as I still don't understand. But if this is the only approach, then slogging through it will be a good learning experience for me I'm sure

    Thanks again!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Give it a shot, if you can't get it to work post back your efforts and we'll work on it
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2008
    Posts
    7
    Thanks ALOT for the help, appreciate it!

    Here are the codes:
    __________________________________________________ _______________
    Sub ShowMessage()

    MsgBox ("ARFCN : 79 (E-GSM)" _
    & vbCr & "L2 Pseudo Length : 18" _
    & vbCr & "Skip indicator : 0" _
    & vbCr & "Protocol discriminator : (6) Radio resources management messages" _
    & vbCr & "Message type : 27")
    End Sub
    __________________________________________________ _______________
    Sub Open_One_Or_Many_Files()
    Dim vaFiles As Variant
    Dim i As Long

    vaFiles = Application.GetOpenFilename _
    (FileFilter:="Text files (*.txt),*.txt", _
    Title:="Open File(s)", MultiSelect:=True)

    If Not IsArray(vaFiles) Then Exit Sub

    With Application
    .ScreenUpdating = False
    For i = 1 To UBound(vaFiles)
    Workbooks.Open vaFiles(i)
    Next i
    .ScreenUpdating = True
    End With

    End Sub
    __________________________________________________ _______________

    The 2nd part, it allows me to open whichever text file that I would like to open, but how can edit the codes to let it open in a messagebox rather than a new Excel workbook?

    And also, running the 2 parts together, my original messagebox is gone haha. Why is it so?

    Sorry if it's too long or draggy, I'm new to Excel VBA

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can't believe I forgot this: http://www.dbforums.com/showpost.php...8&postcount=35
    This utilises the FileSystemObject to grab a file, like your Application.GetOpenFilename does, however using the FSO you can limit it to only allowing a single selected file.

    Anyhow, to modify your code to read the text files first line try this
    Code:
    vaFiles = Application.GetOpenFilename _
    (FileFilter:="Text files (*.txt),*.txt", _
    Title:="Open File(s)", MultiSelect:=True)
    
    If Not IsArray(vaFiles) Then Exit Sub
    
    Dim fs As Object
    Dim filePath As String
    Dim a As Variant
    Const ForAppending As Integer = 8
    
        'Grab the first file from the array
        filePath = vaFiles(0)
            'MsgBox filePath
    
        Set fs = CreateObject("Scripting.FileSystemObject")
    
        Set a = fs.OpenTextFile(filePath, ForAppending)
    
            'Display first line
            MsgBox a.ReadLine
    
        a.Close
        Set a = Nothing
        Set fs = Nothing
    George
    Home | Blog

  8. #8
    Join Date
    Sep 2008
    Posts
    7
    Hi George, sorry for the late reply and thanks again!

    I opened the FSO Access database, but I couldn't view the codes. And anyway, I have tried the codes that you mentioned:

    __________________________________________________ ___________
    Sub ShowMessage()

    MsgBox ("ARFCN : 79 (E-GSM)" _
    & vbCr & "L2 Pseudo Length : 18" _
    & vbCr & "Skip indicator : 0" _
    & vbCr & "Protocol discriminator : (6) Radio resources management messages" _
    & vbCr & "Message type : 27")
    End Sub
    __________________________________________________ ___________
    Sub Open_One_Or_Many_Files()

    Dim vaFiles As Variant

    vaFiles = Application.GetOpenFilename _
    (FileFilter:="Text files (*.txt),*.txt", _
    Title:="Open File(s)", MultiSelect:=True)

    If Not IsArray(vaFiles) Then Exit Sub

    Dim fs As Object
    Dim filePath As String
    Dim a As Variant
    Const ForAppending As Integer = 8

    'Grab the first file from the array
    filePath = vaFiles(0)
    'MsgBox filePath

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set a = fs.OpenTextFile(filePath, ForAppending)

    'Display first line
    MsgBox a.ReadLine

    a.Close
    Set a = Nothing
    Set fs = Nothing
    End Sub
    __________________________________________________ ___________

    However, this appears:

    Run-time error '9':
    Subscript out of range

    I have highlighted the error part in red. Why is it so?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try vaFiles(1) instead?
    George
    Home | Blog

  10. #10
    Join Date
    Sep 2008
    Posts
    7
    I have tried, and there is a new error:
    Run-time error '54':
    Bad file mode.

    Code:
    Sub Open_One_Or_Many_Files()
    
    Dim vaFiles As Variant
    
    vaFiles = Application.GetOpenFilename _
    (FileFilter:="Text files (*.txt),*.txt", _
    Title:="Open File(s)", MultiSelect:=True)
    
    If Not IsArray(vaFiles) Then Exit Sub
    
    Dim fs As Object
    Dim filePath As String
    Dim a As Variant
    Const ForAppending As Integer = 8
    
        'Grab the first file from the array
        filePath = vaFiles(1)
            'MsgBox filePath
    
        Set fs = CreateObject("Scripting.FileSystemObject")
    
        Set a = fs.OpenTextFile(filePath, ForAppending)
    
            'Display first line
            MsgBox a.ReadLine
    
        a.Close
        Set a = Nothing
        Set fs = Nothing
    End Sub
    Very sorry to trouble you again George

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh dear, spotted the mistake...
    Code:
    Set a = fs.OpenTextFile(filePath, ForAppending)
    
        MsgBox a.ReadLine
    We need to open the file ForReading!
    George
    Home | Blog

  12. #12
    Join Date
    Sep 2008
    Posts
    7
    Hi georgev, thanks for the help!

Posting Permissions

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