(excel) Someone made me a macro, but it only works sometimes, locate the error? How?
I am not well versed in vb, so everything might be over my head.
I receive several files each week in .xls format with specific information (ie, the column headings are the same, the information in them is different, depending on who sends it). Unfortunately, that xls format will not go into the software that it needs to. So, it all gets entered by hand. The only way to process it 'electronically' is by converting it into a .txt with specific spacing. Someone created a macro that converts the xls into a .txt. However, it is very buggy. It works for some lines of the workbook but not others. I seem to get different errors each time so I don't know how to fix the problem. If there is anyone who would be willing to look at it (I could also post it here as it isn't very long) and maybe tell me where the errors are coming from? Also, I realize that if the people sending the excel file change things around, this might be causing the problem?
Function PadLeft(text As Variant, totalLength As Integer, padCharacter As String) As String
PadLeft = String(totalLength - Len(CStr(text)), padCharacter) & CStr(text)
End Function
Function PadRight(text As Variant, totalLength As Integer, padCharacter As String) As String
If Len(CStr(text)) > totalLength Then PadRight = CStr(text) Else PadRight = CStr(text) & String(totalLength - Len(CStr(text)), padCharacter)
End Function
Not sure how to attach the sample stuff. It seems to work up to a point. Either, it can only do the first few lines (example, say the excel file has 27 lines, so you would in the range of line 2 to line 27, but it will show only a certain percentage complete, you can keep backing out and trying line 2 to 20, for example, and see where it gets stuck.
Otherwise, it might do all the lines you ask of it, but after the first few columns, the rest do not line up as they should on the output txt file.
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
I'm really having trouble following that. First off, put the code in code tags (click the # symbol, which gives you the begin and the end tags, code goes in between). After I got it somewhat cleaned up, I still can't tell what the intent is exactly. Give us some more detail, like "I need to create the text file from the Excel file which has X columns (number of rows varies), and each field needs to be Y characters wide." Again, sample data would help as well.
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
Originally Posted by vbfbryce
I'm really having trouble following that. First off, put the code in code tags (click the # symbol, which gives you the begin and the end tags, code goes in between). After I got it somewhat cleaned up, I still can't tell what the intent is exactly. Give us some more detail, like "I need to create the text file from the Excel file which has X columns (number of rows varies), and each field needs to be Y characters wide." Again, sample data would help as well.
It is meant to take an excel file with 31 columns (A through AE)
Each column should be of a particular length (some are 1 space, some are 20 spaces, etc.) and it should take this excel file and change it to a txt file that keeps the spacing required.
And again, sorry for my ignorance. I can't seem to attach an xls file?
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
I use the below code to scrub unwanted characters and retain only the numbers and text. I use this mainly for fieldnames but you could drop (comment) any of the tests for text you want to keep.
Code:
Function ScrubData(strFldName As String, Optional iMaxCharacters As Integer = 64) As String
' Remove illegal characters from string. Fieldname Max Char = 64
''' Non-printable characters
''' Grave Accent (`), Exclamation mark (!)
''' Period (.), bracket ([])
''' Leading Space (Include any space to ease VBA coding)
''' question mark (?), at sign (@), quotation mark(")
''' apostrophe('), number sign (#), percent sign (%)
''' a tilde (~), semicolon (;), bracket ([])
''' brace ({}), parenthesis (()), vertical bar(|).
''' Greater than sign (>), Less than sign (<), Minus sign(-)
''' Plus sign (+), Period (.), Asterisk (*),Caret (^)
''' Colon (:), Backslash (\), Slash mark (/)
''' Ampersand (&), Equal sign (=)
Dim newString As String
Dim lpcnt As Integer
newString = strFldName
For lpcnt = 0 To 47 ' Non-Printables Space ! " # $ % & ' ( ) * - + ,
newString = Replace(newString, Chr(lpcnt), "")
Next
For lpcnt = 58 To 64 ' ; : < = > ? @
newString = Replace(newString, Chr(lpcnt), "")
Next
For lpcnt = 91 To 96 ' [ \ ] ^ `
newString = Replace(newString, Chr(lpcnt), "")
Next
For lpcnt = 123 To 126 ' { | } ~
newString = Replace(newString, Chr(lpcnt), "")
Next
For lpcnt = 128 To 255 ' Extended Ascii Characters
newString = Replace(newString, Chr(lpcnt), "")
Next
ScrubData = Left(newString, iMaxCharacters)
End Function
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
There is a sheet that you use to put in the information. That sheet is called "macro" I have included a screenshot. I am not sure how to get the code of that sheet?
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
If you hit Alt+F11 to open the code editor, you should get something similar to what I've attached. If you double click the "Macro" sheet object in the upper left (my version in blue), you should see the code tied to that button.
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
you can test this to see if it works correctly for you, it seemed correct with your file that did not work
test with multiple data files
i added a macro sheet to the same workbook, though i assume that it would normally be in some other workbook, which should still work fine
Code:
Sub sadek()
Dim myFile As String, textline As String, shtmac As Worksheet, l()
Dim shtdata As Worksheet, rw As Long, col As Integer, s As String, stxt As String, strdata As String
l = Array(20, 15, 1, 40, 20, 21, 2, 9, 8, 1, 1, 1, 9, 1, 15, 10, 10, 11, 8, 1, 8, 1, 3, 1, 30, 30, 20, 2, 9, 10, 12)
Set shtmac = ThisWorkbook.Sheets("Macro")
Set shtdata = Workbooks(shtmac.Range("c2").Value).Sheets(shtmac.Range("c3").Value)
myFile = shtmac.Range("c6")
shtmac.Range("C8").NumberFormat = "0%"
For rw = shtmac.Range("C4").Value To shtmac.Range("C5").Value
For col = 1 To 31
stxt = shtdata.Cells(rw, col)
Select Case col
Case 9, 19, 21: stxt = Format(stxt, "yyyymmdd")
Case 23: stxt = Format(stxt, "0.0")
End Select
s = Space(l(col - 1))
Mid(s, 1) = stxt
strdata = strdata & s
Next
strdata = strdata & vbNewLine
shtmac.Range("c8").Value = (((rw - shtmac.Range("c4").Value) / (shtmac.Range("c5").Value - shtmac.Range("c4").Value)))
DoEvents
Next
Open myFile For Output As #1
Print #1, strdata
Close #1
MsgBox ("Done ")
End Sub
i found some conflict between the cell addresses from the macro sheet, i used from c2 to c8 based on your image
i found an anomaly in column W
the fixed length shows as 3, but the format of "0.0" would return longer strings for 2 digit values in sample file eg. 14 would return "14.0"
the above code truncates the string to 3 characters "14."
if this is not the desired result you would need to specify how you would like it handled
the progress percent was not possible to track as it updated and finished too fast to see
in a big file it should only update every 200 to 500 rows
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
Originally Posted by westconn1
you can test this to see if it works correctly for you, it seemed correct with your file that did not work
test with multiple data files
i added a macro sheet to the same workbook, though i assume that it would normally be in some other workbook, which should still work fine
Code:
Sub sadek()
Dim myFile As String, textline As String, shtmac As Worksheet, l()
Dim shtdata As Worksheet, rw As Long, col As Integer, s As String, stxt As String, strdata As String
l = Array(20, 15, 1, 40, 20, 21, 2, 9, 8, 1, 1, 1, 9, 1, 15, 10, 10, 11, 8, 1, 8, 1, 3, 1, 30, 30, 20, 2, 9, 10, 12)
Set shtmac = ThisWorkbook.Sheets("Macro")
Set shtdata = Workbooks(shtmac.Range("c2").Value).Sheets(shtmac.Range("c3").Value)
myFile = shtmac.Range("c6")
shtmac.Range("C8").NumberFormat = "0%"
For rw = shtmac.Range("C4").Value To shtmac.Range("C5").Value
For col = 1 To 31
stxt = shtdata.Cells(rw, col)
Select Case col
Case 9, 19, 21: stxt = Format(stxt, "yyyymmdd")
Case 23: stxt = Format(stxt, "0.0")
End Select
s = Space(l(col - 1))
Mid(s, 1) = stxt
strdata = strdata & s
Next
strdata = strdata & vbNewLine
shtmac.Range("c8").Value = (((rw - shtmac.Range("c4").Value) / (shtmac.Range("c5").Value - shtmac.Range("c4").Value)))
DoEvents
Next
Open myFile For Output As #1
Print #1, strdata
Close #1
MsgBox ("Done ")
End Sub
i found some conflict between the cell addresses from the macro sheet, i used from c2 to c8 based on your image
i found an anomaly in column W
the fixed length shows as 3, but the format of "0.0" would return longer strings for 2 digit values in sample file eg. 14 would return "14.0"
the above code truncates the string to 3 characters "14."
if this is not the desired result you would need to specify how you would like it handled
the progress percent was not possible to track as it updated and finished too fast to see
in a big file it should only update every 200 to 500 rows
Thanks so much, I will try this!
As for the code on that 'front' page. Curious thing, but even double clicking or right clicking and asking to view the code turns up a blank page. Is there a way that it could be hidden?
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
I double clicked on the area shown by the arrow, and also tried to right click and selected 'view code', but it just opens up that blank screen with nothing in it?
Re: (excel) Someone made me a macro, but it only works sometimes, locate the error? H
where is the Sadek procedure? that is the code that should run when the go button is clicked
right click the Go button >veiw code (or assign macro)
on testing my code with 20,000 rows, i made some changes, like
Code:
Dim myFile As String, textline As String, shtmac As Worksheet, l()
Dim shtdata As Worksheet, rw As Long, col As Integer, s As String, stxt As String, strdata As String
l = Array(20, 15, 1, 40, 20, 21, 2, 9, 8, 1, 1, 1, 9, 1, 15, 10, 10, 11, 8, 1, 8, 1, 3, 1, 30, 30, 20, 2, 9, 10, 12)
Set shtmac = ThisWorkbook.Sheets("Macro")
Set shtdata = Workbooks(shtmac.Range("c2").Value).Sheets(shtmac.Range("c3").Value)
myFile = shtmac.Range("c6")
t = Timer
Open myFile For Output As #1
shtmac.Range("C8").NumberFormat = "0%"
For rw = shtmac.Range("C4").Value To shtmac.Range("C5").Value
For col = 1 To 31
stxt = shtdata.Cells(rw, col)
Select Case col
Case 9, 19, 21: stxt = Format(stxt, "yyyymmdd")
Case 23: stxt = Format(stxt, "0.0")
End Select
s = Space(l(col - 1))
Mid(s, 1) = stxt
strdata = strdata & s
Next
' strdata = strdata & vbNewLine
Print #1, strdata
strdata = ""
If rw Mod 200 = 0 Or rw = shtmac.Range("c5") Then
shtmac.Range("c8").Value = (((rw - shtmac.Range("c4").Value) / (shtmac.Range("c5").Value - shtmac.Range("c4").Value)))
DoEvents
End If
Next
Close #1
MsgBox ("Done in " & Timer - t & " secs")
End Sub
you may need a small modification like, if last row is 0 work till end of file (last row of data, but as i have no idea how many rows your data is likely to contain, i am not sure if it would be required
in case it is not clear l is an array containing the fixed string length for each column
processing time of about 4.2 seconds
with some further optimizing i was able to reduce processing time by about .3 of a second
Last edited by westconn1; May 2nd, 2015 at 06:59 PM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete