+ Reply to Thread
Results 1 to 7 of 7

Export / Import to / from Text file

  1. #1
    C_Ascheman
    Guest

    Export / Import to / from Text file

    1)What I am wanting to do is export 1 cell ("I4") to a text file when the
    focus is moved from that cell.
    2)When the form is opened I want to be able to import the number from the
    text file, and increase it by 1.

    My employer wants to have the number that is in the "I4" cell to be auto
    increased every time he opens that form. I have some programming experience
    just not alot with importing or exporting anything to anything. Any help or
    suggestions will be greatly appreciated.

  2. #2
    Tom Ogilvy
    Guest

    Re: Export / Import to / from Text file

    You can get some useful information on reading and writing files here:

    http://www.cpearson.com/excel/imptext.htm import/export text files

    http://www.applecore99.com/gen/gen029.asp

    More information on reading and writing text files from VBA

    http:/www.cpearson.com/excel/events.htm
    information on Chip Pearson's site on events. The Workbook_Open event can
    be used to execute code when opening a workbook.

    --
    Regards,
    Tom Ogilvy


    "C_Ascheman" <[email protected]> wrote in message
    news:[email protected]...
    > 1)What I am wanting to do is export 1 cell ("I4") to a text file when the
    > focus is moved from that cell.
    > 2)When the form is opened I want to be able to import the number from the
    > text file, and increase it by 1.
    >
    > My employer wants to have the number that is in the "I4" cell to be auto
    > increased every time he opens that form. I have some programming

    experience
    > just not alot with importing or exporting anything to anything. Any help

    or
    > suggestions will be greatly appreciated.




  3. #3
    C_Ascheman
    Guest

    Re: Export / Import to / from Text file

    Sorry Tom I tried figuring it out from the link you gave me but im have a
    really difficult time with this. Here is what I have even though its wrong.
    Can you please tell me how to fix this.

    Private sub Worksheet_Activate()
    intGrFile = FreeFile
    Open "C:\GR.txt" For Input As intGrFile
    Range("I4").value = intGrFile + 1
    Close intGrFile
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    intGrFile = FreeFile
    Open "C:\GR.txt" For Output As intGrFile
    If Range("I4").value > intGrFile then
    intGrFile = Range("I4").value
    End If
    Close intGrFile
    End Sub

    Any help greatly appreciated from anyone.
    Thanks


    "Tom Ogilvy" wrote:

    > You can get some useful information on reading and writing files here:
    >
    > http://www.cpearson.com/excel/imptext.htm import/export text files
    >
    > http://www.applecore99.com/gen/gen029.asp
    >
    > More information on reading and writing text files from VBA
    >
    > http:/www.cpearson.com/excel/events.htm
    > information on Chip Pearson's site on events. The Workbook_Open event can
    > be used to execute code when opening a workbook.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "C_Ascheman" <[email protected]> wrote in message
    > news:[email protected]...
    > > 1)What I am wanting to do is export 1 cell ("I4") to a text file when the
    > > focus is moved from that cell.
    > > 2)When the form is opened I want to be able to import the number from the
    > > text file, and increase it by 1.
    > >
    > > My employer wants to have the number that is in the "I4" cell to be auto
    > > increased every time he opens that form. I have some programming

    > experience
    > > just not alot with importing or exporting anything to anything. Any help

    > or
    > > suggestions will be greatly appreciated.

    >
    >
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: Export / Import to / from Text file

    Private Sub Worksheet_Activate()
    intGrFile = FreeFile
    Open "C:\GR.txt" For Input As intGrFile
    Line Input #intGrFile, sStr
    Range("I4").Value = CLng(sStr) + 1
    Close intGrFile
    Open "C:\GR.txt" For Output As intGrFile
    Print #intGrFile, Range("I4").Value
    Close intGrFile
    End Sub

    is a basic approach. You might need to put in some more conditions to
    insure the you only increase once on each opening.

    Another approach is to store the value in a defined name

    insert=>Name=>Define
    name: cnt
    refersto: =6

    In I4 you would have

    =cnt

    and you could have your code increment cnt.

    --
    Regards,
    Tom Ogilvy


    "C_Ascheman" <[email protected]> wrote in message
    news:[email protected]...
    > Sorry Tom I tried figuring it out from the link you gave me but im have a
    > really difficult time with this. Here is what I have even though its

    wrong.
    > Can you please tell me how to fix this.
    >
    > Private sub Worksheet_Activate()
    > intGrFile = FreeFile
    > Open "C:\GR.txt" For Input As intGrFile
    > Range("I4").value = intGrFile + 1
    > Close intGrFile
    > End Sub
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > intGrFile = FreeFile
    > Open "C:\GR.txt" For Output As intGrFile
    > If Range("I4").value > intGrFile then
    > intGrFile = Range("I4").value
    > End If
    > Close intGrFile
    > End Sub
    >
    > Any help greatly appreciated from anyone.
    > Thanks
    >
    >
    > "Tom Ogilvy" wrote:
    >
    > > You can get some useful information on reading and writing files here:
    > >
    > > http://www.cpearson.com/excel/imptext.htm import/export text files
    > >
    > > http://www.applecore99.com/gen/gen029.asp
    > >
    > > More information on reading and writing text files from VBA
    > >
    > > http:/www.cpearson.com/excel/events.htm
    > > information on Chip Pearson's site on events. The Workbook_Open event

    can
    > > be used to execute code when opening a workbook.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "C_Ascheman" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 1)What I am wanting to do is export 1 cell ("I4") to a text file when

    the
    > > > focus is moved from that cell.
    > > > 2)When the form is opened I want to be able to import the number from

    the
    > > > text file, and increase it by 1.
    > > >
    > > > My employer wants to have the number that is in the "I4" cell to be

    auto
    > > > increased every time he opens that form. I have some programming

    > > experience
    > > > just not alot with importing or exporting anything to anything. Any

    help
    > > or
    > > > suggestions will be greatly appreciated.

    > >
    > >
    > >




  5. #5
    C_Ascheman
    Guest

    Re: Export / Import to / from Text file

    Ok I did as you said Tom but I am still having issues. On the intGrFile its
    throwing a message of "variable not defined". Not sure what to define it as.
    I tried setting ' Dim intGrFile as NewFile ' in Option Explicit but no good.
    As of this moment I have nothing in my Option Explicit, and am unsure of how
    to correct the issue. Here is what I have so far in my code.

    Private Sub Worksheet_Activate()
    intGrFile = FreeFile
    Open "C:\GR.txt" For Input As intGrFile
    Line Input #intGrFile, sStr
    Range("I4").Value = CLng(sStr) + 1
    Close intGrFile
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    intGrFile = FreeFile
    Open "C:\Gr.txt" For Output As intGrFile
    Line Input #intGrFile, sStr
    If Range("I4").Value > sStr Then
    Print #intGrFile, Range("I4").Value
    End If
    Close intGrFile
    End Sub

    The code that is in the Worksheet_Activate should I put this code instead in
    Workbook_Activate of something. As it is now the Worksheet I am working on is
    the only worksheet in the workbook so it never appears to be activated.
    Thanks for you help so far.

    "Tom Ogilvy" wrote:

    > Private Sub Worksheet_Activate()
    > intGrFile = FreeFile
    > Open "C:\GR.txt" For Input As intGrFile
    > Line Input #intGrFile, sStr
    > Range("I4").Value = CLng(sStr) + 1
    > Close intGrFile
    > Open "C:\GR.txt" For Output As intGrFile
    > Print #intGrFile, Range("I4").Value
    > Close intGrFile
    > End Sub
    >
    > is a basic approach. You might need to put in some more conditions to
    > insure the you only increase once on each opening.
    >
    > Another approach is to store the value in a defined name
    >
    > insert=>Name=>Define
    > name: cnt
    > refersto: =6
    >
    > In I4 you would have
    >
    > =cnt
    >
    > and you could have your code increment cnt.
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "C_Ascheman" <[email protected]> wrote in message
    > news:[email protected]...
    > > Sorry Tom I tried figuring it out from the link you gave me but im have a
    > > really difficult time with this. Here is what I have even though its

    > wrong.
    > > Can you please tell me how to fix this.
    > >
    > > Private sub Worksheet_Activate()
    > > intGrFile = FreeFile
    > > Open "C:\GR.txt" For Input As intGrFile
    > > Range("I4").value = intGrFile + 1
    > > Close intGrFile
    > > End Sub
    > >
    > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > > intGrFile = FreeFile
    > > Open "C:\GR.txt" For Output As intGrFile
    > > If Range("I4").value > intGrFile then
    > > intGrFile = Range("I4").value
    > > End If
    > > Close intGrFile
    > > End Sub
    > >
    > > Any help greatly appreciated from anyone.
    > > Thanks
    > >
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > > > You can get some useful information on reading and writing files here:
    > > >
    > > > http://www.cpearson.com/excel/imptext.htm import/export text files
    > > >
    > > > http://www.applecore99.com/gen/gen029.asp
    > > >
    > > > More information on reading and writing text files from VBA
    > > >
    > > > http:/www.cpearson.com/excel/events.htm
    > > > information on Chip Pearson's site on events. The Workbook_Open event

    > can
    > > > be used to execute code when opening a workbook.
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > >
    > > > "C_Ascheman" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 1)What I am wanting to do is export 1 cell ("I4") to a text file when

    > the
    > > > > focus is moved from that cell.
    > > > > 2)When the form is opened I want to be able to import the number from

    > the
    > > > > text file, and increase it by 1.
    > > > >
    > > > > My employer wants to have the number that is in the "I4" cell to be

    > auto
    > > > > increased every time he opens that form. I have some programming
    > > > experience
    > > > > just not alot with importing or exporting anything to anything. Any

    > help
    > > > or
    > > > > suggestions will be greatly appreciated.
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Tom Ogilvy
    Guest

    Re: Export / Import to / from Text file

    Private Sub Worksheet_Activate()
    Dim intGrFile as Long, sStr as String
    intGrFile = FreeFile
    Open "C:\GR.txt" For Input As intGrFile
    Line Input #intGrFile, sStr
    Range("I4").Value = CLng(sStr) + 1
    Close intGrFile
    Open "C:\GR.txt" For Output As intGrFile
    Print #intGrFile, Range("I4").Value
    Close intGrFile
    End Sub


    --
    Regards,
    Tom Ogilvy


    "C_Ascheman" <[email protected]> wrote in message
    news:[email protected]...
    > Ok I did as you said Tom but I am still having issues. On the intGrFile

    its
    > throwing a message of "variable not defined". Not sure what to define it

    as.
    > I tried setting ' Dim intGrFile as NewFile ' in Option Explicit but no

    good.
    > As of this moment I have nothing in my Option Explicit, and am unsure of

    how
    > to correct the issue. Here is what I have so far in my code.
    >
    > Private Sub Worksheet_Activate()
    > intGrFile = FreeFile
    > Open "C:\GR.txt" For Input As intGrFile
    > Line Input #intGrFile, sStr
    > Range("I4").Value = CLng(sStr) + 1
    > Close intGrFile
    > End Sub
    >
    > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > intGrFile = FreeFile
    > Open "C:\Gr.txt" For Output As intGrFile
    > Line Input #intGrFile, sStr
    > If Range("I4").Value > sStr Then
    > Print #intGrFile, Range("I4").Value
    > End If
    > Close intGrFile
    > End Sub
    >
    > The code that is in the Worksheet_Activate should I put this code instead

    in
    > Workbook_Activate of something. As it is now the Worksheet I am working on

    is
    > the only worksheet in the workbook so it never appears to be activated.
    > Thanks for you help so far.
    >
    > "Tom Ogilvy" wrote:
    >
    > > Private Sub Worksheet_Activate()
    > > intGrFile = FreeFile
    > > Open "C:\GR.txt" For Input As intGrFile
    > > Line Input #intGrFile, sStr
    > > Range("I4").Value = CLng(sStr) + 1
    > > Close intGrFile
    > > Open "C:\GR.txt" For Output As intGrFile
    > > Print #intGrFile, Range("I4").Value
    > > Close intGrFile
    > > End Sub
    > >
    > > is a basic approach. You might need to put in some more conditions to
    > > insure the you only increase once on each opening.
    > >
    > > Another approach is to store the value in a defined name
    > >
    > > insert=>Name=>Define
    > > name: cnt
    > > refersto: =6
    > >
    > > In I4 you would have
    > >
    > > =cnt
    > >
    > > and you could have your code increment cnt.
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "C_Ascheman" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Sorry Tom I tried figuring it out from the link you gave me but im

    have a
    > > > really difficult time with this. Here is what I have even though its

    > > wrong.
    > > > Can you please tell me how to fix this.
    > > >
    > > > Private sub Worksheet_Activate()
    > > > intGrFile = FreeFile
    > > > Open "C:\GR.txt" For Input As intGrFile
    > > > Range("I4").value = intGrFile + 1
    > > > Close intGrFile
    > > > End Sub
    > > >
    > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > > > intGrFile = FreeFile
    > > > Open "C:\GR.txt" For Output As intGrFile
    > > > If Range("I4").value > intGrFile then
    > > > intGrFile = Range("I4").value
    > > > End If
    > > > Close intGrFile
    > > > End Sub
    > > >
    > > > Any help greatly appreciated from anyone.
    > > > Thanks
    > > >
    > > >
    > > > "Tom Ogilvy" wrote:
    > > >
    > > > > You can get some useful information on reading and writing files

    here:
    > > > >
    > > > > http://www.cpearson.com/excel/imptext.htm import/export text

    files
    > > > >
    > > > > http://www.applecore99.com/gen/gen029.asp
    > > > >
    > > > > More information on reading and writing text files from VBA
    > > > >
    > > > > http:/www.cpearson.com/excel/events.htm
    > > > > information on Chip Pearson's site on events. The Workbook_Open

    event
    > > can
    > > > > be used to execute code when opening a workbook.
    > > > >
    > > > > --
    > > > > Regards,
    > > > > Tom Ogilvy
    > > > >
    > > > >
    > > > > "C_Ascheman" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > 1)What I am wanting to do is export 1 cell ("I4") to a text file

    when
    > > the
    > > > > > focus is moved from that cell.
    > > > > > 2)When the form is opened I want to be able to import the number

    from
    > > the
    > > > > > text file, and increase it by 1.
    > > > > >
    > > > > > My employer wants to have the number that is in the "I4" cell to

    be
    > > auto
    > > > > > increased every time he opens that form. I have some programming
    > > > > experience
    > > > > > just not alot with importing or exporting anything to anything.

    Any
    > > help
    > > > > or
    > > > > > suggestions will be greatly appreciated.
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  7. #7
    C_Ascheman
    Guest

    Re: Export / Import to / from Text file

    I got it figured out. In the Workbook I have this code:

    Option Explicit
    Dim sStr As String

    Private Sub Workbook_Open()
    Open "C:\GR.txt" For Input As #1
    Input #1, sStr
    Sheet1.Range("I4").Value = CLng(sStr) + 1
    Close #1
    End Sub

    In the Worksheet I have this:

    Option Explicit
    Dim sStr As String

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Open "C:\Gr.txt" For Output As #1
    If Range("I4").Value > sStr Then
    Write #1, Range("I4").Value
    End If
    Close #1
    End Sub

    Thanks for all your help Tom. It got me going in the right direction.

    C_Ascheman


    "Tom Ogilvy" wrote:

    > Private Sub Worksheet_Activate()
    > Dim intGrFile as Long, sStr as String
    > intGrFile = FreeFile
    > Open "C:\GR.txt" For Input As intGrFile
    > Line Input #intGrFile, sStr
    > Range("I4").Value = CLng(sStr) + 1
    > Close intGrFile
    > Open "C:\GR.txt" For Output As intGrFile
    > Print #intGrFile, Range("I4").Value
    > Close intGrFile
    > End Sub
    >
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "C_Ascheman" <[email protected]> wrote in message
    > news:[email protected]...
    > > Ok I did as you said Tom but I am still having issues. On the intGrFile

    > its
    > > throwing a message of "variable not defined". Not sure what to define it

    > as.
    > > I tried setting ' Dim intGrFile as NewFile ' in Option Explicit but no

    > good.
    > > As of this moment I have nothing in my Option Explicit, and am unsure of

    > how
    > > to correct the issue. Here is what I have so far in my code.
    > >
    > > Private Sub Worksheet_Activate()
    > > intGrFile = FreeFile
    > > Open "C:\GR.txt" For Input As intGrFile
    > > Line Input #intGrFile, sStr
    > > Range("I4").Value = CLng(sStr) + 1
    > > Close intGrFile
    > > End Sub
    > >
    > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > > intGrFile = FreeFile
    > > Open "C:\Gr.txt" For Output As intGrFile
    > > Line Input #intGrFile, sStr
    > > If Range("I4").Value > sStr Then
    > > Print #intGrFile, Range("I4").Value
    > > End If
    > > Close intGrFile
    > > End Sub
    > >
    > > The code that is in the Worksheet_Activate should I put this code instead

    > in
    > > Workbook_Activate of something. As it is now the Worksheet I am working on

    > is
    > > the only worksheet in the workbook so it never appears to be activated.
    > > Thanks for you help so far.
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > > > Private Sub Worksheet_Activate()
    > > > intGrFile = FreeFile
    > > > Open "C:\GR.txt" For Input As intGrFile
    > > > Line Input #intGrFile, sStr
    > > > Range("I4").Value = CLng(sStr) + 1
    > > > Close intGrFile
    > > > Open "C:\GR.txt" For Output As intGrFile
    > > > Print #intGrFile, Range("I4").Value
    > > > Close intGrFile
    > > > End Sub
    > > >
    > > > is a basic approach. You might need to put in some more conditions to
    > > > insure the you only increase once on each opening.
    > > >
    > > > Another approach is to store the value in a defined name
    > > >
    > > > insert=>Name=>Define
    > > > name: cnt
    > > > refersto: =6
    > > >
    > > > In I4 you would have
    > > >
    > > > =cnt
    > > >
    > > > and you could have your code increment cnt.
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > >
    > > > "C_Ascheman" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > Sorry Tom I tried figuring it out from the link you gave me but im

    > have a
    > > > > really difficult time with this. Here is what I have even though its
    > > > wrong.
    > > > > Can you please tell me how to fix this.
    > > > >
    > > > > Private sub Worksheet_Activate()
    > > > > intGrFile = FreeFile
    > > > > Open "C:\GR.txt" For Input As intGrFile
    > > > > Range("I4").value = intGrFile + 1
    > > > > Close intGrFile
    > > > > End Sub
    > > > >
    > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    > > > > intGrFile = FreeFile
    > > > > Open "C:\GR.txt" For Output As intGrFile
    > > > > If Range("I4").value > intGrFile then
    > > > > intGrFile = Range("I4").value
    > > > > End If
    > > > > Close intGrFile
    > > > > End Sub
    > > > >
    > > > > Any help greatly appreciated from anyone.
    > > > > Thanks
    > > > >
    > > > >
    > > > > "Tom Ogilvy" wrote:
    > > > >
    > > > > > You can get some useful information on reading and writing files

    > here:
    > > > > >
    > > > > > http://www.cpearson.com/excel/imptext.htm import/export text

    > files
    > > > > >
    > > > > > http://www.applecore99.com/gen/gen029.asp
    > > > > >
    > > > > > More information on reading and writing text files from VBA
    > > > > >
    > > > > > http:/www.cpearson.com/excel/events.htm
    > > > > > information on Chip Pearson's site on events. The Workbook_Open

    > event
    > > > can
    > > > > > be used to execute code when opening a workbook.
    > > > > >
    > > > > > --
    > > > > > Regards,
    > > > > > Tom Ogilvy
    > > > > >
    > > > > >
    > > > > > "C_Ascheman" <[email protected]> wrote in message
    > > > > > news:[email protected]...
    > > > > > > 1)What I am wanting to do is export 1 cell ("I4") to a text file

    > when
    > > > the
    > > > > > > focus is moved from that cell.
    > > > > > > 2)When the form is opened I want to be able to import the number

    > from
    > > > the
    > > > > > > text file, and increase it by 1.
    > > > > > >
    > > > > > > My employer wants to have the number that is in the "I4" cell to

    > be
    > > > auto
    > > > > > > increased every time he opens that form. I have some programming
    > > > > > experience
    > > > > > > just not alot with importing or exporting anything to anything.

    > Any
    > > > help
    > > > > > or
    > > > > > > suggestions will be greatly appreciated.
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1