| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export / Import to / from Text file
You can get some useful information on reading and writing files he
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 he 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 he 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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 he 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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 he 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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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 he 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" wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Import and Export Text data | Excel Discussion (Misc queries) | |||
| How can I export text from excel autoshapes to a text file? | Excel Discussion (Misc queries) | |||
| Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) | |||
| Import/Export Text File Tab Delimiter | Excel Programming | |||
| Export into text file | Excel Programming | |||
Linear Mode
