Solved

Formatting notepad contents from an Excel marco.

Posted on 2004-10-15
14
355 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now