Aiysha
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.
Thank you.
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.
Thankyou.
So you need to create a file from your data in a specific format?
~IM
~IM
ASKER
yes, Excel->Notepad (formating)->another application.
Thank you.
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 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(OutputF ile, Overwrite) 'Create file for output
End If
RowCount = 1
Do Until Sheets(OutSheet).Cells(Row Count, 1) = Empty
For ColLoop = StartCol To EndCol
FileObj.Write Format(Sheets(OutSheet).Ce lls(RowCou nt, 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.
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(OutputF
End If
RowCount = 1
Do Until Sheets(OutSheet).Cells(Row
For ColLoop = StartCol To EndCol
FileObj.Write Format(Sheets(OutSheet).Ce
'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.
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.
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
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
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.
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.
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.
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.
*************
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=========================
**************************
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=========================
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I need spaces in between the cells. But I could figure that out. Thank you.
What exactly are you trying to do?
~IM