Solved

Formatting notepad contents from an Excel marco.

Posted on 2004-10-15
14
370 Views
Last Modified: 2010-05-02
In my excel Macro I open the notepad and work through sendkeys to format the data (i.e. I want to delete some rows in notepad file, add/delete few charanters from specific lines). Can someone please direct me to resources to such programming.

Thank you.
0
Comment
Question by:Aiysha
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12320883
There are easier ways to work with data in excel thru VB code.

What exactly are you trying to do?

~IM
0
 

Author Comment

by:Aiysha
ID: 12321013
I know working in Excel is much easier, but this cant be done in excel, thats why I have to make the changes in notepad.. I am trying to change the width of a cell and as a result all the column's width is getting changed..The program that takes notepad file in very spave sensitive, so I have to make all the changes in notepad itself.

Thankyou.
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12321101
So you need to create a file from your data in a specific format?

~IM
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Aiysha
ID: 12321137
yes, Excel->Notepad (formating)->another application.

Thank you.
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12321193
Does your notepad-formatted file need columns of a certain no. of characters?
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 12321362
Where jimbobmcgee and I are going with this is that you can create a text file directly with VB code and format it however you want.  This will be far more reliable than attempting to "automate" an instance of Notepad.

If you explain the format of the file and where the data should come from we can give you the necessary code.

~IM
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12321371
If it does need columns of a certain no. of chars, try:

Sub ExportToFile()
   
    'User input
    Const Overwrite     As Boolean = True           'Overwrite existing file?
    Const OutSheet      As String = "Sheet1"        'Sheet to save
    Const StartCol        As Integer = 1                 'Save columns 1(A)...
    Const EndCol          As Integer = 5                 '...to 5(E)
    Const Delim            As String = "|"                'Delimeter
   
    Dim WidthArray()
        WidthArray = Array("", "@@@@@@@@", "@@@@@", "@@@@@@@", "@@", "@@@@")
                        'Put a "@@@", string for each column in your sheet. Each @ is a forced character
                        'Make the first one a blank ("") to account for the 0 lower bound
   
    'System variables
    Dim OutputFile      As String
    Dim FSO               As New Scripting.FileSystemObject
    Dim FileObj           As Object
    Dim RowCount       As Integer
    Dim ColLoop          As Integer
   
    'Code begin
    OutputFile = InputBox("Enter output filename", "Export")

        If OutputFile = "" Then
            MsgBox "No export file specified.", vbCritical, "Export"
            Exit Sub
        Else
            Set FileObj = FSO.CreateTextFile(OutputFile, Overwrite)       'Create file for output
        End If

    RowCount = 1
   
    Do Until Sheets(OutSheet).Cells(RowCount, 1) = Empty

        For ColLoop = StartCol To EndCol
           
           FileObj.Write Format(Sheets(OutSheet).Cells(RowCount, ColLoop).Value), WidthArray(ColLoop)    
                                                                                                           'Write formatted cell to file
   
           If ColLoop < EndCol Then FileObj.Write Delim                            'Write the delimeter

        Next ColLoop

        RowCount = RowCount + 1
        FileObj.Write vbCrLf                   'Write a new line into the file

    Loop
   
    FileObj.Close
   
    MsgBox UCase(OutputFile) & " successfully created.", vbInformation, "Export"
           
End Sub

To write the fixed-width cells...

HTH

J.
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12321397
With the above code, change:

     Const Delim            As String = "|"                'Delimeter

to

     Const Delim            As String = ","                'Delimeter

if you need a comma delimeted file, or use the delimeter you require: e.g. "/"

J.
0
 
LVL 1

Expert Comment

by:erickaye22
ID: 12321508
I wouldn't make the changes in notepad.  I would just create the text file (notepad file) from an excel macro.  You can format the data anyway you want when you write it directly to the a text file.  Would something like this help you?  Create a new macro in excel and add the code below.  This will create a new text file with all the data on the spread sheet.  You can add spaces or whatever, before writing out the value to a file.

    Dim sFile As String
    Dim oRange As Range
    Dim oCell As Range
   
    'Open File for writing
    On Error Resume Next
    sFile = "C:\File.txt"
    Open sFile For Output As #1
    If Err.Number > 0 Then
        MsgBox "Problems with " & sFile & ".  Error: " & Err.Description
        Exit Sub
    End If

    iCount = 0
    Set oRange = ActiveSheet.UsedRange
    On Error GoTo 0
    For Each oCell In oRange
        If Len(oCell.Value) Then
            Print #1, , Spc(5); oCell.Value  'puts five spaces in front of the value in the file
        End If
    Next
    Set oRange = Nothing
    Close #1
0
 

Author Comment

by:Aiysha
ID: 12338885
erickaye22,

Thanx for ur help. This is what I need. But your code copies value of each cell on new line. I am trying to format this code by trial and error process, but can u resend me a code which copies the data from excel exactly in the same format except for few cells that will need spaces deleted from the left.

Thanx a lot.
0
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12345605
>> But your code copies value of each cell on new line

Try changing:

     Print #1, , Spc(5); oCell.Value  

to:

     Print #1, , Spc(5); oCell.Value;     '<- THE ';' AT THE END PREVENTS THE NEW LINE

Do you have any sample input/output you can post? -- I'm sure someone here could format it for you.  Alternatively, is the file you are creating supposed to be tab-delimeted (like an Excel text file) -- if so, perhaps using:
     
     Print #1, oCell.Value & vbTab;

may help you.

HTH

J.
0
 

Author Comment

by:Aiysha
ID: 12373897
Example of .txt file from .xls

*************
THIS IS WHAT I GET
***************

NEW ITEMS
INJ    1                    1                     1                        1                       1
  10.65
   0.0
INJ    1                    3                      0.                      0.                       -100000.                               0.
PROD   2                 10                    10                      3                          1
 10.9054
  1000.0
PROD   2                1                       20000.                   0.                 0.                           0.
C=====================================DATA SET 2================================================

*********************************
DESIRED OUTPUT
********************************
NEW ITEMS
INJ    1  1  1  1  1           <----- See the difference between the digits
  10.65
   0.0
INJ    1  3        0.        0.  -100000.        0.
PROD   2 10 10  3  1
 10.9054
  1000.0
PROD   2  1    20000.        0.        0.        0.
C=====================================DATA SET 2================================================


Thank you.
0
 
LVL 1

Accepted Solution

by:
erickaye22 earned 500 total points
ID: 12374070
Sorry for the delay, put have been out of town.  Here is code that does row by row.  Do you want spaces in between the cells or tabs?

    Dim oRange As Range
    Dim oCell As Range
    Dim oRow As Range
   
    'Open File for writing
    On Error Resume Next
    sFile = "C:\File.txt"
    Open sFile For Output As #1
    If Err.Number > 0 Then
        MsgBox "Problems with " & sFile & ".  Error: " & Err.Description
        Exit Sub
    End If

    iCount = 0
    Set oRange = ActiveSheet.UsedRange
    On Error GoTo 0
    Dim sRow As String
    For Each oRow In oRange.Rows
        sRow = ""
        For Each oCell In oRow.Cells
                sRow = sRow & oCell.Value & Chr(32)
        Next
        sRow = Trim(sRow) 'get rid of any spaces at the end
        Print #1, sRow
    Next
    Set oRange = Nothing
    Close #1
0
 

Author Comment

by:Aiysha
ID: 12445440
I need spaces in between the cells. But I could figure that out. Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question