Link to home
Start Free TrialLog in
Avatar of Aiysha
AiyshaFlag for United States of America

asked on

Formatting notepad contents from an Excel marco.

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.
Avatar of Mike Tomlinson
Mike Tomlinson
Flag of United States of America image

There are easier ways to work with data in excel thru VB code.

What exactly are you trying to do?

~IM
Avatar of Aiysha

ASKER

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.
So you need to create a file from your data in a specific format?

~IM
Avatar of Aiysha

ASKER

yes, Excel->Notepad (formating)->another application.

Thank you.
Does your notepad-formatted file need columns of a certain no. of characters?
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
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.
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.
Avatar of erickaye22
erickaye22

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
Avatar of Aiysha

ASKER

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.
>> 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.
Avatar of Aiysha

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of erickaye22
erickaye22

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aiysha

ASKER

I need spaces in between the cells. But I could figure that out. Thank you.