Posts by markc

    Re: Import data from txt file

    Thanks Andy

    I sent an e-mail to the folks who send me the txt file
    (SQL server)

    I'll have to wait to see what they have to say

    I opened in xl and I see that lines 6 and 7 should really be part of line 5
    Assumed since I don't see the order # at the front of the string
    Am I correct?


    Re: Import data from txt file

    Thanks Andy -
    Can I see the <CR> is some way?
    I searched for but nothing found
    Can you tell me line and space number?

    Maybe if I go back to the folks who send me the file
    and tell them a specific example they can figure something out


    Hi all
    Using xl2003

    I need to import certain fields from a text file
    Tried using ImportTextFile from Chip pearson [code at end]
    Results were kind of sporadic

    Just need to pull
    price variation

    and dump to the activesheet.activecell
    [Sample of txt file attached]


    Re: cutomize toolbars use icon instead of text

    Thanks Will

    Works great, which I'm sure you realize :) :)

    A little bit cheezy that I have to first create
    a cutom button utilizing faceid, copy the button image, paste the button

    The standard paletteof 42 icons is far too limiting.

    Thanks again

    Hi all
    Using xl2003

    I want to customize a toolbar
    I go through all the steps
    And drag Advanced Filter... to the toolbar
    Problem is, this takes up valuable real estate

    How can I replace with an icon?
    I have jwalk's faceid.xla if that is of any assistance


    Re: Workbook name as string

    Thanks all

    Here's my current code

    This returns a run-time error '9':
    Subscript out of range

    All I'm trying to do is to make sure that
    has the focus prior to importing large amount of data from
    csv file

    Here is the offending line:

    I also tried:


    Re: Workbook name as string

    Thanks TJ
    Yes it is
    I just want some fault tolerance in case
    I try to run and the wb does not have the focus.

    Need to make sure the sql import [DoTheImport]
    isiported exactly where specified so no ill consequence


    Hi all
    Using xl 2003

    Trying to refer to workbook with variable after
    assiging full name as string :

    Problem is this line, can't figure out why

    Workbooks.Activate wb1

    I've tried writing it 10 different ways, no help.
    Can anyone give me a point in the right direction?


    Re: toggle window visibility

    Thanks Ray and Dangelor.
    Works great

    Ray -
    Yup, want to keep all macros avail, but take personal.xls out of the equation when tiling windows. And by using just one macro to handle the visible/invisible, saves hotkey shortcut memorization


    Re: Morefunc nbtext

    Dennis/Zack -

    Seems Laurent is aware of the issue
    His website:


    Last update : "log10" error fixed (NBTEXT function)

    I deleted all morefunc files, downloaded new morefunc.exe, reinstalled
    nbtext still hangs Excel. (2003 11.6355.6360 SP1)

    Have not tried any of the other functions
    Will do so and post back


    Hi all.

    Using xl 2003

    Downloaded and installed Morefunc.exe from Laurent Longre


    Tried to insert the nbtext function
    XL simply hangs
    Formula bar shows = nbtext()
    I cannot click on anything or proceed in anyway

    Has anyone run across this?

    Re: toggle window visibility

    Me again -

    Recorded macro and saw part of my problem
    Revised :

    Sub Toggle_Personal_Vis()
        If ActiveWindow.Visible = False Then
            ActiveWindow.Visible = True Else:
            ActiveWindow.Visible = False
        End If
    End Sub

    Still not working
    Compile error syntax error
    I've tried many variations moving the Else statement everywhere and line continuation. Nothing seems to work


    Hi all.

    In addition to storing frquently used macros in personal.xls
    I also store frequently used formulas, lists, and other doodads

    Sometimes I want to see everything in personal, but sometimes when tiling I want personal hidden from view.

    I created a macro and assigned ctrl+shift+p to it, but can't seem to get it to work.

    Sub Toggle_Personal_Vis()
        If Workbooks("Personal.xls").Visible = False Then
        Workbooks("Personal.xls").Visible = True
            Else: Workbooks("Personal.xls").Visible = False
        End If
    End Sub

    But not working.
    I also tried Alt+F8 and running the macro - no luck
    Do I need fully distinguished path? c:\program files\....


    Re: output to txt file

    Alan -


    Are you saying 'that' only two lines were output and you were expecting 7?



    With respect to my comment on using archive or archive2. Once you have run the process and you are happy with the results then I would delete the old archive.txt and rename the file archive2.txt to archive.txt. Then append future archive data to that. This will limit the ongoing growth of the archive file. For an audit trail at the moment I would not change the process.

    Sounds good. Thanks.

    Files are attached

    Re: output to txt file

    Thanks Alan
    Awesome as always :)


    Whether you continue to use the original archive file or the archive2.txt file is up to you

    If I use archive.txt, code errors out here

    '    On Error GoTo DiskError
         'Open input and output files
        intFileIn = FreeFile()
        Open strArchiveFilename2 For Input As #intFileIn

    If I use archive2.txt, the code executes

    I copied a few lines of interspersed records into archive.txt
    I then opened with xl as csv,sorted and manually checked for uniqueness

    In my sample of order 6024, I found 7 unique lines.
    Col AE = qty
    Col AG = unit price
    Col AO = SKU

    The Catering sales people or customer used SKU ...171 twice, but the quantity was different. Don't know why they would do this, but then I'm just an accounting/data geek.

    I searched for 6024 on the full archive file
    Textpad returned occurrences at these rows
    7 lines as expected
    No other groupings were found during the manual search

    I ran the code, 2 lines were written out to "orders"
    SKU460-180-184 line 5 of sample
    SKU460-180-181 line 6 of sample

    Sorry so long
    Thanks again for all of your efforts :)