Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
I have office professional 2003. The way it is supposed to work is
file is saved as a .txt file and opened in Notepad. Select all and copy from Notepad. Open the Excel Spreadsheet and use the Visual Basic Code to paste the information into Excel. It works well and good except for 6 cells which all do a similar change. Inc. in the notepad becomes Ic. in Excel, Main in notepad becomes Mai in Excel, or All becomes A11 in excel. Any suggestions. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
When you write "The way it is supposed to work ", what is the "it" you're
referring to. The behavior you describe certainly isn't default behavior for Office 2003 so I have to assume you're talking about some user protocol or perhaps a VBA subroutine. If this is a VBA routine, is there some reason why Notepad is even being brought into the mix? Steve wrote in message ... I have office professional 2003. The way it is supposed to work is file is saved as a .txt file and opened in Notepad. Select all and copy from Notepad. Open the Excel Spreadsheet and use the Visual Basic Code to paste the information into Excel. It works well and good except for 6 cells which all do a similar change. Inc. in the notepad becomes Ic. in Excel, Main in notepad becomes Mai in Excel, or All becomes A11 in excel. Any suggestions. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
You are right it isn't a default behavior and it is a VBA subroutine.
It is an export of information from a website which is a custome export which is saved as a .txt file. Because the file is a .txt file it is opened in notepad to select all and copy the information for the ability to paste it into the excel spreadsheet using the vba subroutine to select cells and paste the information. The .txt file has the informaiton correct, but the pasted cells change text to numbers or leave out text all together. On Jan 7, 8:48*pm, "Steve Yandl" wrote: When you write "The way it is supposed to work ", what is the "it" you're referring to. *The behavior you describe certainly isn't default behavior for Office 2003 so I have to assume you're talking about some user protocol or perhaps a VBA subroutine. *If this is a VBA routine, is there some reason why Notepad is even being brought into the mix? Steve wrote in message ... I have office professional 2003. *The way it is supposed to work is file is saved as a .txt file and opened in Notepad. *Select all and copy from Notepad. *Open the Excel Spreadsheet and use the Visual Basic Code to paste the information into Excel. *It works well and good except for 6 cells which all do a similar change. *Inc. in the notepad becomes Ic. in Excel, Main in notepad becomes Mai in Excel, or All becomes A11 in excel. *Any suggestions.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
Excel can read text files, particularly if they have a recognizable
structure. This can be readily automated. Alternatively you can use time-honored VB/A routines, or the newer FSO, to read the text files and process the data into various cells. No need to Notepad nor for the user hassle of opening text files and copying etc. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ wrote in message ... You are right it isn't a default behavior and it is a VBA subroutine. It is an export of information from a website which is a custome export which is saved as a .txt file. Because the file is a .txt file it is opened in notepad to select all and copy the information for the ability to paste it into the excel spreadsheet using the vba subroutine to select cells and paste the information. The .txt file has the informaiton correct, but the pasted cells change text to numbers or leave out text all together. On Jan 7, 8:48 pm, "Steve Yandl" wrote: When you write "The way it is supposed to work ", what is the "it" you're referring to. The behavior you describe certainly isn't default behavior for Office 2003 so I have to assume you're talking about some user protocol or perhaps a VBA subroutine. If this is a VBA routine, is there some reason why Notepad is even being brought into the mix? Steve wrote in message ... I have office professional 2003. The way it is supposed to work is file is saved as a .txt file and opened in Notepad. Select all and copy from Notepad. Open the Excel Spreadsheet and use the Visual Basic Code to paste the information into Excel. It works well and good except for 6 cells which all do a similar change. Inc. in the notepad becomes Ic. in Excel, Main in notepad becomes Mai in Excel, or All becomes A11 in excel. Any suggestions.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
As Jon comments, if you don't read the text file into Excel directly, you
could use the "Scripting.FileSystemObject" (the FSO he refers to) to work with the text file. In fact, the object "MSXML2.XMLHTTP" has a 'ResponseText' property that might allow you to fetch the text directly from the web page. Going back to your original issue, I suspect there are typographical errors on the web page that somehow get disguised by Notepad (perhaps because of the font choice) and then only get revealed after placing the text in Excel. That's just a guess. Steve Yandl wrote in message ... You are right it isn't a default behavior and it is a VBA subroutine. It is an export of information from a website which is a custome export which is saved as a .txt file. Because the file is a .txt file it is opened in notepad to select all and copy the information for the ability to paste it into the excel spreadsheet using the vba subroutine to select cells and paste the information. The .txt file has the informaiton correct, but the pasted cells change text to numbers or leave out text all together. On Jan 7, 8:48 pm, "Steve Yandl" wrote: When you write "The way it is supposed to work ", what is the "it" you're referring to. The behavior you describe certainly isn't default behavior for Office 2003 so I have to assume you're talking about some user protocol or perhaps a VBA subroutine. If this is a VBA routine, is there some reason why Notepad is even being brought into the mix? Steve wrote in message ... I have office professional 2003. The way it is supposed to work is file is saved as a .txt file and opened in Notepad. Select all and copy from Notepad. Open the Excel Spreadsheet and use the Visual Basic Code to paste the information into Excel. It works well and good except for 6 cells which all do a similar change. Inc. in the notepad becomes Ic. in Excel, Main in notepad becomes Mai in Excel, or All becomes A11 in excel. Any suggestions.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
On Jan 8, 9:44 am, "Steve Yandl" wrote:
As Jon comments, if you don't read the text file into Excel directly, you could use the "Scripting.FileSystemObject" (the FSO he refers to) to work with the text file. In fact, the object "MSXML2.XMLHTTP" has a 'ResponseText' property that might allow you to fetch the text directly from the web page. Going back to your original issue, I suspect there are typographical errors on the web page that somehow get disguised by Notepad (perhaps because of the font choice) and then only get revealed after placing the text in Excel. That's just a guess. Steve Yandl wrote in message ... You are right it isn't a default behavior and it is a VBA subroutine. It is an export of information from a website which is a custome export which is saved as a .txt file. Because the file is a .txt file it is opened in notepad to select all and copy the information for the ability to paste it into the excel spreadsheet using the vba subroutine to select cells and paste the information. The .txt file has the informaiton correct, but the pasted cells change text to numbers or leave out text all together. On Jan 7, 8:48 pm, "Steve Yandl" wrote: When you write "The way it is supposed to work ", what is the "it" you're referring to. The behavior you describe certainly isn't default behavior for Office 2003 so I have to assume you're talking about some user protocol or perhaps a VBA subroutine. If this is a VBA routine, is there some reason why Notepad is even being brought into the mix? Steve wrote in message ... I have office professional 2003. The way it is supposed to work is file is saved as a .txt file and opened in Notepad. Select all and copy from Notepad. Open the Excel Spreadsheet and use the Visual Basic Code to paste the information into Excel. It works well and good except for 6 cells which all do a similar change. Inc. in the notepad becomes Ic. in Excel, Main in notepad becomes Mai in Excel, or All becomes A11 in excel. Any suggestions.- Hide quoted text - - Show quoted text - I use Office Pro 2003 while others may still be using earlier versions of the Excel. Would the FSO still allow this spreadsheet to be backwards compatible? How would I go about having the script copy information without first knowing where the information is? The way I was trained to use the system was to use the notepad to 'copy' the info to be loaded into the spread sheet. The website only allows information download with an export with certain fields of information. The file is downloaded and saved on the users computer, which changes the file name each time it is downloaded, and then using notepad to open the file, select all, and copy all the data with a right click, and then click on a button on the worksheet of the spreadsheet with a macro to paste the information into spreadsheet. The information is then placed into necessary cells of the spreadsheet by excel commands like ='worksheet1'!C9. The typo errors seems plausible, but I still wonder why it would leave out characters as well as change them. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Notepad Visual Basic and Excel
The "Scripting.FileSystemObject" is part of the scripting runtime libraries
and should be present on all but ancient PCs running Microsoft Windows. With it, you can read a text file in its entirety or line by line and assign the text read to string variables. There are a number of VBA functions you can use to break down the text (for example, only extract text between a set of markers) before placing it in worksheet cells or doing something else with it. You ask "How would I go about having the script copy information without first knowing where the information is?" How do you know where the information is now? Whether you're doing the copy and paste manually or having a VBA routine do it, you must have some way to locate the text file and then locate the needed text within that file. Steve Yandl I use Office Pro 2003 while others may still be using earlier versions of the Excel. Would the FSO still allow this spreadsheet to be backwards compatible? How would I go about having the script copy information without first knowing where the information is? The way I was trained to use the system was to use the notepad to 'copy' the info to be loaded into the spread sheet. The website only allows information download with an export with certain fields of information. The file is downloaded and saved on the users computer, which changes the file name each time it is downloaded, and then using notepad to open the file, select all, and copy all the data with a right click, and then click on a button on the worksheet of the spreadsheet with a macro to paste the information into spreadsheet. The information is then placed into necessary cells of the spreadsheet by excel commands like ='worksheet1'!C9. The typo errors seems plausible, but I still wonder why it would leave out characters as well as change them. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual BASIC in Excel | Excel Worksheet Functions | |||
Visual BASIC in Excel | Excel Worksheet Functions | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
Excel and Visual Basic | Excel Discussion (Misc queries) | |||
visual basic in excel - | Excel Discussion (Misc queries) |