Sometime a task like Concatenation frustrates you because you would like to concatenate a range, and it takes lot of time doing those repetitive tasks. Well, here’s a way to get it simple. The procedure illustrated here allows you to choose the input range, concatenation character, and the output range. If you use the function, then you can enter the optional concatenate character (by default it is a comma (,)), and the input range. – Selva V Pasupathy

While working on files and folders, many a time I come across a situation where I want the list of all filenames in a specific folder or directory. I tried out a code that helps me a lot. – Selva V Pasupathy

Sub GetFileNames()
Dim sPath As String, sFileNm As String
sPath = “C:DW”
‘You can also use Application.GetOpenFilename to get a file name from a folder,
‘and then extract the Directory name from that string
‘You can also use filters with GetOpenFilenam such as *.txt, see Help on this topic
sFileNm = Dir(sPath, vbNormal) ‘Get the first file from the specified directory
‘Start a loop
Do While sFileNm <> “”
     ‘If the file has a dbf extension then print the file name
     If Right(sFileNm, 3) = “dbf” Then
         Debug.Print sFileNm
     End If
    sFileNm = Dir
Loop
End Sub

I have been trying to solve a problem where we can get the range from the user so that the macro can be run once the range is selected. I have been visiting sites and going through all the reference to find out but not able to locate what was the problem. I already knew that there is something wrong in my code… I still am not able to find why sometime the code does not work… I am documenting a code that works well…

- Selva V Pasupathy

I have Listed some of the sites I keep going to whre I find lots of interesting and updated information.

- Selva V Pasupathy

One of the examples I came across while going through www.ozgrid.com is as follows. Maybe this will help you understanding how vb works while filling webforms. If you comeacross something interesting please keep me posted.  -selvavinaygam

Sub Going_Postal()
Dim ie As Object
Set ie = CreateObject(“InternetExplorer.Application”)
On Error GoTo errHandler

With ie
    .navigate “http://www.canadapost.ca/personal/tools/pcl/bin/quick-e.asp“
    .Visible = True
   

    Do While .busy: DoEvents: Loop
    Do While .ReadyState <> 4: DoEvents: Loop
    With .document.Forms(1)
        ‘Stree Number
        .street_number.Value = “10132″
        ‘Stree Name
        .street_name.Value = “Imperial Street”
        ‘City
        .city.Value = “Chilliwack”
        ‘AB = Alberta, BC = British Columbia, MB = Manitoba, NB = New Brunswick, _
            NL = Newfoundland and Labrador, NS = Nova Scotia, NT = Northwest Territories, _
            NU = Nunavut, ON = Ontario, PE = Prince Edward Island, QC = Quebec, _
            SK = Saskatchewan, YT = Yukon
        .prov.Value = “BC”
        .submit
    End With
    Do While Not CBool(InStrB(1, .document.URL, _
        “cp_search_response-e.asp”))
        DoEvents
    Loop
    Do While .busy: DoEvents: Loop
    Do While .ReadyState <> 4: DoEvents: Loop
    MsgBox .document.all.tags(“table”).Item(11).Rows(1).Cells(7).innerText
End With

errHandler:
ie.Quit: Set ie = Nothing
End Sub

Many a times I feel I need to keep all information orderly even if I delete the file, I should have the data unformatted somewhere. I know it will be difficult and timeconsuming to index it or archive it and we simply delete it thinking that we wont need that information again only to find that we need that information urgently and we are not able to find it.

I think the following code can help a great lot of we run it against the event and store info in a specific folder as a backup and this will help us index and archive information in one folder regardless of whether we delete the file or not.

Sub PrintToTextFile()
Dim FileNum As Integer
Dim i As Long
Dim sPath, sInputLine As String

    sPath = "C:\Documents and Settings\My Documents\"
    FileNum = FreeFile          '   Next free filenumber

        Open sPath & "TextFile.txt" For Output As #FileNum
        Open "C:\FOLDERNAME\TEXTFILE.TXT" For Append As #FileNum
        sInputLine = "This line is written by a code"
        Print #FileNum, sInputLine & " " & Time
        Close #FileNum ' close the file
End Sub

The following code can be used to read the file.

Sub Read_TextFile()
Dim FileNum As Integer
Dim i As Long
Dim sPath, sInputLine As String

    sPath = "C:\Documents and Settings\My Documents\"
    Dim InputString As String

' Reading a text file line by line
' This example macro shows how you can read a textfile line by line:

    FileNum = FreeFile      'next free filenumber
    Open sPath & "TEXTFILE.TXT" For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, InputString ' read a line from the textfile
        Debug.Print InputString ' do something with the string
       'MsgBox InputString      ' also you can use msgbox
    Wend
    Close #FileNum
End Sub

-Selva V Pasupathy

I have been trying to find out a way where one can rename a file (of whatever type). I was able to do it before but never been successful in getting a comprehensive solution. I came across the following codes (I do not remember the name of the website) that can be used to change filenames one or many. This maybe useful to archive a folder with large number of files.
 

Sub Rename_File(ByVal strOldName As String, ByVal strNewName As String)
    Name strOldName As strNewName
End Sub