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?
As adatapost pointed out, you need to look up the following in help or online...
Line Input Function
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
Edited by vb5prgrmr: n/a
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.
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?
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
Dim MyChar() As Byte, I As Integer MyChar = StrConv("ThisWord", vbFromUnicode) For I = 0 To UBound(MyChar) MsgBox Chr(MyChar(I)) Next I
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.
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...