[mso] Re: Excel Macro

Well...Chip Pearson is a great guy and an MVP...and you can find him
working in the Excel programming newsgroups. So you might want to just
go post in there and mention his stuff. Chances are he, or someone other
Excel VBA MVP will provide assistance. You can get there via
http://support.microsoft.com/newsgroups.

Or just drop him a note! Not all folks take incoming calls<g>...but the
worst that can happen is you don't get an answer and you have to resort
to the NGs. But knowing Chip...I'll bet you get a reply.

Although I don't know Excel and haven't read all that you posted...most
code go into one module. I believe it's like Word, click
Insert/Module...a new mod will be inserted which you can click in the
PROPERTIES windows to rename if you wish. Then copy all the code
straight from the info to  the module one after another. The editor
knows to create breaks between sub procedures. Here's a little dialog
creation article I wrote that has some Insert/Mod screen shots that
might give you an idea how it works. 

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=15

But like I said...try dropping him a note at chip@xxxxxxxxxxx and see if
he can help you clarify the issues. He might appreciate the fact that
users need additional info to understand what he's written. Obviously,
you don't want to bug folks and EXPECT their assistance, but a nice note
explaining you appreciate his work and asking for a little clarification
usually gets a good response.

Good luck!


Dian Chapman
Technical Consultant, Instructor,
Microsoft MVP & TechTrax Editor

Word AutoForm/VBA eBook: http://www.mousetrax.com/books.html
Tutorial web site: http://www.mousetrax.com/techpage.html
TechTrax Ezine: http://www.mousetrax.com/techtrax/

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Joe Trimarchi
Sent: Tuesday, October 29, 2002 7:04 PM
To: mso@xxxxxxxxxxxxx
Subject: [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
*************************************************************

*************************************************************
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: