Notepad Visual Basic and Excel

[mtooley74]

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.

 

[Steve Yandl]

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

 

[mtooley74]

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.

 

[Jon Peltier]

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
_______


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.

 

[Steve Yandl]

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


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.

 

[mtooley74]

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


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.

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.

 

[Steve Yandl]

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

 

[Jon Peltier]

It's not too big a deal to write a routine using GetOpenFileName so the user
can browse and select a file, then the program can process the file.

- Jon