Solved

Formatting notepad contents from an Excel marco.

Posted on 2004-10-15
14
363 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB6 code to programmatically convert pdf to excel 21 89
Windows 10 start screen issues 9 55
VB6 - Convert HH:MM into Decimal 8 54
Spell Check in VB6 13 112
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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

770 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