open an unknown file with Visual Basic

[rejina]

I have to develop a code with visual basic to open a file then read each line of the text file one string at a time then output these strings to individual cells in excel.
Can anyone help?

[kvprajapati]

>open an unknown file with Visual Basic

Open statement.

[vb5prgrmr]

As adatapost pointed out, you need to look up the following in help or online...

FreeFile Function
Open Statement
Line Input Function
Close Statement

Then, use your friends (yahoo, google, ask, answers, bing) and search for vb6 excel tutorial or vb6 automating excel...

Edit: Also while searching, vb6 common dialog tutorial

Good Luck

[AndreRet]

Firstly, to avoid possible errors, check if the file is opened by another application. The remote file to be tested can be specified using either mapped drives (e.g. z:\filename.exe), UNC format (e.g \\server\share\filename.ext), machine name (e.g. \\vbnetdev\share\filename.ext), or IP address within the network (e.g \\192.168.1.102\share\filename.ext).

In your project with a form containing one text box (Text1), one label (Label1) and a command button (Command1). Add the following code -

Option Explicit

Private Const GENERIC_READ As Long = &H80000000
Private Const INVALID_HANDLE_VALUE As Long = -1
Private Const OPEN_EXISTING As Long = 3
Private Const FILE_ATTRIBUTE_NORMAL As Long = &H80
Private Const MAX_PATH As Long = 260

'Enum containing values representing
'the status of the file
Private Enum IsFileResults
   FILE_IN_USE = -1  'True
   FILE_FREE = 0     'False
   FILE_DOESNT_EXIST = -999 'arbitrary number, other than 0 or -1
End Enum

Private Type FILETIME
   dwLowDateTime As Long
   dwHighDateTime As Long
End Type

Private Type WIN32_FIND_DATA
   dwFileAttributes As Long
   ftCreationTime As FILETIME
   ftLastAccessTime As FILETIME
   ftLastWriteTime As FILETIME
   nFileSizeHigh As Long
   nFileSizeLow As Long
   dwReserved0 As Long
   dwReserved1 As Long
   cFileName As String * MAX_PATH
   cAlternate As String * 14
End Type

Private Declare Function CreateFile Lib "kernel32" _
   Alias "CreateFileA" _
  (ByVal lpFileName As String, _
   ByVal dwDesiredAccess As Long, _
   ByVal dwShareMode As Long, _
   ByVal lpSecurityAttributes As Long, _
   ByVal dwCreationDisposition As Long, _
   ByVal dwFlagsAndAttributes As Long, _
   ByVal hTemplateFile As Long) As Long
    
Private Declare Function CloseHandle Lib "kernel32" _
  (ByVal hFile As Long) As Long
  
Private Declare Function FindFirstFile Lib "kernel32" _
   Alias "FindFirstFileA" _
  (ByVal lpFileName As String, _
   lpFindFileData As WIN32_FIND_DATA) As Long

Private Declare Function FindClose Lib "kernel32" _
  (ByVal hFindFile As Long) As Long



Private Sub Form_Load()

   Command1.Caption = "IsFileInUse"
   Text1.Text = "d:\test.doc"
   Label1.Caption = ""
   
End Sub


Private Sub Command1_Click()

   Dim bResult As IsFileResults
   
   bResult = IsFileInUse(Text1.Text)

   Select Case bResult
      Case FILE_IN_USE
         Label1.Caption = "File in use"
         
      Case FILE_FREE
         Label1.Caption = "File is available"
         
      Case FILE_DOESNT_EXIST
         Label1.Caption = "File does not exist!"
         
   End Select
   
   Label1.Caption = Label1.Caption & "  (" & bResult & ")"
   
End Sub


Private Function IsFileInUse(sFile As String) As IsFileResults

   Dim hFile As Long
   
   If FileExists(sFile) Then
   
     'note that FILE_ATTRIBUTE_NORMAL (&H80) has
     'a different value than VB's constant vbNormal (0)!
      hFile = CreateFile(sFile, _
                         GENERIC_READ, _
                         0, 0, _
                         OPEN_EXISTING, _
                         FILE_ATTRIBUTE_NORMAL, 0&)

     'this will evaluate to either
     '-1 (FILE_IN_USE) or 0 (FILE_FREE)
      IsFileInUse = hFile = INVALID_HANDLE_VALUE

      CloseHandle hFile
   
   Else
   
     'the value of FILE_DOESNT_EXIST in the Enum
     'is arbitrary, as long as it's not 0 or -1
      IsFileInUse = FILE_DOESNT_EXIST
   
   End If
   
End Function


Private Function FileExists(sSource As String) As Boolean

   Dim WFD As WIN32_FIND_DATA
   Dim hFile As Long
   
   hFile = FindFirstFile(sSource, WFD)
   FileExists = hFile <> INVALID_HANDLE_VALUE
   
   Call FindClose(hFile)
   
End Function

This will create a test app you can use to enter a file, remote or local, and have the tool lock the file for testing the code above. Note that the Open method used will create any mistyped filename, so check for stray 0-length files after testing.

Private hFile As Long

Private Sub Form_Load()

   Text1.Text = ""
   Me.Caption = "ready"

End Sub


Private Sub Command1_Click()

   On Local Error GoTo oops
   hFile = FreeFile
   
   Open Text1.Text For Binary Access Read Lock Read As #hFile
   Caption = "file handle: " & hFile
     
oops_out:
   Exit Sub
   
oops:
   MsgBox Err.Number & vbCrLf & Err.Description
   Resume oops_out
End Sub


Private Sub Command2_Click()

   Close
   hFile = 0
   Me.Caption = "ready"
   
End Sub

Most well-behaved applications open and lock files to prevent inadvertent or deliberate changes to the file contents by other applications while the file is being used. Others, like Notepad, open the file, read the data, then close the file immediately without placing a lock on the file.

This routine is for use with the former access method, and will return True if another application has the file open, or False if not. For files opened with apps like Notepad, it will always return false. For apps like WordPad, it will return True if both the file and app are on the local machine, but False if file is on a remote machine. (Some locks apparently are different than others, and the CreateFile and CreateFileEx APIs does have special flags that can be used by applications to indicate other apps can open files for joint access, both for read and write.)

Assuming then that the application opening the file places a proper lock on the file, this routine will correctly detect if:

a local file has been opened and locked by an application running on the local machine, a local file has been opened and locked by an application running on a remote machine, a remote file has been opened and locked by an application running on the local machine, a remote file has been opened and locked by an application running on another remote machine.

Now to open a file.
Description: Gives access to File Explorer capabilities and shows
progress dialog and / or error dialogs

Place the following code in a Module -

Public Const FO_MOVE As Long = &H1
Public Const FO_COPY As Long = &H2
Public Const FO_DELETE As Long = &H3
Public Const FO_RENAME As Long = &H4
Public Const FOF_MULTIDESTFILES As Long = &H1
Public Const FOF_CONFIRMMOUSE As Long = &H2
Public Const FOF_SILENT As Long = &H4
'Public Const FOF_RENAMEONCOLLISION As Long = &H8
Public Const FOF_NOCONFIRMATION As Long = &H10
Public Const FOF_WANTMAPPINGHANDLE As Long = &H20
Public Const FOF_CREATEPROGRESSDLG As Long =&H0
'Public Const FOF_ALLOWUNDO As Long = &H40
Public Const FOF_FILESONLY As Long = &H80
Public Const FOF_SIMPLEPROGRESS As Long = &H100
Public Const FOF_NOCONFIRMMKDIR As Long = &H200

Type SHFILEOPSTRUCT
     hwnd As Long
     wFunc As Long
     pFrom As String
     pTo As String
     fFlags As Long
     fAnyOperationsAborted As Long
     hNameMappings As Long
     lpszProgressTitle As String
End Type

Declare Function SHFileOperation Lib "Shell32.dll" Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long



'Place the following code under a command button or in a menu, etc...

Dim result As Long, fileop As SHFILEOPSTRUCT
With fileop
        .hwnd = Me.hwnd
        .wFunc = FO_COPY
        .pFrom = "C:\PROGRAM FILES\MICROSOFT VISUAL BASIC\VB.HLP" & vbNullChar & "C:\PROGRAM FILES\MICROSOFT VISUAL BASIC\README.HLP" & vbNullChar & vbNullChar
'       .pFrom = "C:\*.*" & vbNullChar & vbNullChar
        .pTo = "C:\testfolder" & vbNullChar & vbNullChar
        .fFlags = FOF_SIMPLEPROGRESS Or FOF_FILESONLY
End With
result = SHFileOperation(fileop)
If result <> 0 Then
        ' Operation failed
        MsgBox Err.LastDllError 
Else
        If fileop.fAnyOperationsAborted <> 0 Then
                      MsgBox "Operation Failed"
         End If
End If

Another way of opening a text file and read from it will be by using something like -

Dim SQLstr As String
Dim X as String
Dim k
Set cmd = New ADODB.Command

rtext1.LoadFile "d:\rcompanies\1.txt" 'rtext1 is a rich text box.
X = rtext1.Text

With cmd

k = 0

Do While Len(X) > 10
pos = InStr(1, X, ";")
X = Right(X, Len(X) - pos)
k = k + 1
Loop

rtext1.LoadFile "d:\companies\1.txt"
X = rtext1.Text


bar1.Max = k + 1 bar1 is a progressbar
bar1.Value = 1

Do While Len(X) > 10
pos = InStr(1, X, ";")
Data = Left(X, pos)
Data = Trim(Data)
X = Right(X, Len(X) - pos)

    .ActiveConnection = Adodc1.ConnectionString
    .CommandType = adCmdText
    .CommandText = Data
    .Execute

rtext1.Text = X
rtext1.SaveFile "d:\qatarcompanies\2.txt"
Adodc1.Refresh
DataGrid1.ReBind
Sleep 1000
bar1.Value = bar1.Value + 1
DoEvents

Loop

End With

You can now add the text to excel as in the small sample attached.

[rejina]

Thank you for all your effort!! I really appreciate it!

I developed a macro to open that text file into excel sheet.

However I need to split the characters into individual cell of excel.

Like this is what I have got so far

aeshar in one cell.

But it has to be like this-

a e s h a r

each letter should be put into one cell!

Can anyone help?

[vb5prgrmr]

To seperate a word into each character you can do it a couple of different ways.

Dim I As Integer, MyCharacters() As String
ReDim MyCharacters(0 To Len("ThisWord") - 1) As String
For I = 0 To UBound(MyCharacters)
  MsgBox MyCharacters(I)
Next I

Or

Dim MyChar() As Byte, I As Integer
MyChar = StrConv("ThisWord", vbFromUnicode)
For I = 0 To UBound(MyChar)
  MsgBox Chr(MyChar(I))
Next I

Good Luck

[WaltP]

How about

Dim I As Integer, MyCharacters As String
MyCharacters = "ThisWord"
For I = 1 To len(MyCharacters)
  MsgBox mid$(MyCharacters I1)
Next I

It's simple. No redimming or ubounding.

[vb5prgrmr]

Ya know Walt, if you look closely at my first example (that does not work without a piece of your code) you will see I was trying to get there... :) Oh well, I guess that is what I get for posting so late...