[mso] Excel Macro


The following information was copied from:
http://www.cpearson.com/excel/imptext.htm and appears to be exactly what I'm
looking for. i.e. I receive a CD each month with a comma delimited text file
that I can open using the excel wizard. The filename takes the form of
200210.txt with the 10 representing the month. I want to create a Macro that
will ask for Month & Year and then bring the data into Excel starting in
cell A2. The first row A1 will contain the column names. My problem is I
have no experience using the VBA editor, I'm using both Win95b/Office97 &
WinXP/Office 2000. I want to copy and paste the code below but I don't know
how do to it. Does all of the code below get copied and pasted into one
macro. If so in what order? Do I paste it into a module? So far I haven't
been able to get that to work. I've recorded a few small macros but that is
the extent of my experience.

Thanks for your help,

Joe

Quote
Pearson Software Consulting, LLC

    Importing And Exporting To Text Files

 Importing From Text Files

Excel does not give you a method to import a text file directly into an
existing worksheet.  The  Text Import Wizard will always create a new
workbook.  The macro that follows allows you to import a delimited text file
directly into the active worksheet, beginning in the active cell.

The macro ImportTextFile will import the file specified by FName into the
active worksheet.  Imported values begin in the active cell, and each line
of the text file are placed on the same row of the worksheet. Each
subsequent line of the text file is place on the next row of the worksheet.
Imported values, separated by the character specified by Sep, are placed in
separate columns.  Each line in the text file does not need to contain the
same number of values. If there is a blank line in the text file, a blank
row will appear in the worksheet.

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub


You can call this macro from another VBA procedure as follows:

ImportTextFile "c:\temp\test.txt", ";"

Since this code has parameters, it will not appear in the standard "Macros"
dialog list (ALT+F8).  The following procedure will prompt you for the
filename and delimiter character, and then run the ImportTextFile procedure.

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename  _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
        "Import Text File")
ImportTextFile CStr(FName), Sep

End Sub

The procedure above will import each delimited element on a line in the text
file to a separate cell.
Unquote





*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: