EXTRACT SUMMARY DATA FOR JOURNAL ENTRY

[pdeshazier]

I need to select and format data from accounting system A so that i can then import into accounting system B. From system A, i have data which looks like this (you'll have to scroll right to see all values):

100000CASH DE TRUST CAP MGT .0027-FebCR 0582 041 SUBCR EMPLOYEE MEALS24.95CR9-MarGJ 0042 001 CXB CR0582 2/27 KEYED WRONG ACCT #224.95.00 *.00 *200.00 *

What i ultimately need are only the 100000 on line 1 and the last value (200.00) on line 4. so... i need a loop which will do something like this:

Copy cell A1 (the account number), then for every line of 21-Feb format, delete it, then copy the account number on the last line in cell a1. That would then get me my account number and month activity amount in one row, so that i could then import into acctg system B.

Make sense??? HELP! :dunno

[smc2911]

My first reaction is that it looks like a job for perl rather than Excel! Also, it looks as though in loading the data in Excel you've processed it somewhat and that will make it tricky to get the data you want. In the example you gave the 200.00 seems to have changed to .00 in the spreadsheet. If you have a version of the extract prior to this pre-processing it would probably be easier. Also, what do you want to extract for longer entries, such as the 100001 entry, which has a lot of figures? All of them? The total?

[pdeshazier]

thanks for replying and sorry for the confusion. actually, the file, as you can see, comes to me in .txt format. i pulled into excel to have individual fields. the 200.00 in my example was just made up so as not to confuse anyone as to why i'd want to do a journal entry for 0.00 dollars. That's another point - if the account's net activity for the month is 0.00, we don't need that to import into the gl system. For the accounts with LOTS of detail, such as teh 100001, i still only want those items i described in my example - acct# and net amount, but both on the same line. all those detail txn amounts (with a date in column A) will just be deleted in the loop since i don't need them.

we don't have perl here, so we're trying to rely on the tools we have, the best ones i thought to be excel and access. is this doable with vba? PLEASE say it is!!

[smc2911]

Could you post the original text file (or at least part of it)?

[pdeshazier]

the file i attached is the actual .txt file; were you able to access it? thanks

[smc2911]

From my end, the attached file looks like a .xls file and even if I save it and try to open it with a text editor, it seems to have quite a bit of binary data in it, consistent with it being an excel file.

Sean.

[pdeshazier]

so sorry. trying to attach the txt file, but get error msg invalid file type. let me try zipping it

[pdeshazier]

zipped the .txt file. can you view it now?

[smc2911]

Yes I can. I'll try to take a look this evening.

[pdeshazier]

thanks so much

[smc2911]

Well, I've been having an attempt, but not much luck so far. VBA is not really designed to be a text processing tool (hence my earlier reference to perl!). Is there any chance of getting the text file extract in a different format? The file you have is really designed to be human-readable rather than machine readable, which means that it's really very fiddly to process. Something comma delimited would be ideal! Anyway, I'll keep trying.

Sean.

[jindon]

Hi,

I don't know what you want i slike this.
If this is so, tell me how you want to output the results..
1. for the first file (.xls)


Sub test()
Dim a, result(), Rex As Object
Dim mItem As Object, myAcc As String, myAmount
Set Rex = CreateObject("VBScript.RegExp")
With Sheets(1)
a = .UsedRange.Value
End With
With Rex
.Pattern = "(\d+\,?)*\d+\.\d{2}\s*$"
For i = 1 To UBound(a, 1)
If .test(a(i, 2)) Then
Set mItem = .Execute(a(i, 2))
myAcc = a(i, 1)
myAmount = mItem.Item(0)
Set mItem = Nothing
If myAmount <> "0.00" Then
n = n + 1
ReDim Preserve result(1 To 2, 1 To n)
result(1, n) = myAcc: myAcc = ""
result(2, n) = myAmount: myAmount = ""
End If
End If
Next
End With
If i = Empty Then GoTo Last
msg = "Acc" & vbTab & ": " & "Amount" & vbLf
For i = 1 To UBound(result, 2)
msg = msg & result(1, i) & vbTab & ": " _
& result(2, i) & vbLf
Next
MsgBox msg
Last:
Erase a, result
Set Rex = Nothing
End Sub
[/vba]
2. for reading directly from text file
[vba]
Sub test2()
Dim a(), i As Long, ff As Integer, txt As String, fn As String
Dim Rex As Object, mItem As Object
Dim myAcc, myAmount, result()
Set Rex = CreateObject("VBScript.RegExp")
fn = "C:" & Application.PathSeparator & "02-2006 GL Detail.txt" '<- alter path
If Dir(fn) = "" Then Exit Sub
ff = FreeFile
Open fn For Input As #ff
Do While Not EOF(ff)
Line Input #ff, txt
With Rex
.Pattern = "^\d+\s"
If .Test(txt) Then
Set mItem = .Execute(txt)
myAcc = mItem.Item(0)
.Pattern = "(\d+\,?)*\d+\.\d{2}\s?$"
If .Test(txt) Then
Set mItem = .Execute(txt)
myAmount = mItem.Item(0)
i = i + 1
ReDim Preserve result(1 To 2, 1 To i)
result(1, i) = myAcc
result(2, i) = myAmount
End If
End If
End With
Loop
Close #ff
If i = Empty Then
MsgBox "No data to extract"
Exit Sub
End If
msg = "Acc#" & vbTab & ": " & "Amount" & vbLf
For i = 1 To UBound(result, 2)
msg = msg & result(1, i) & vbTab & ": " & result(2, i) & vbLf
Next
MsgBox msg, vbQuestion, "Is this what you wanted?"
End Sub

[smc2911]

Looks like I'm off the hook!
Sean.

[pdeshazier]

THANKS! I HAVEN'T yet gotten to look at this in depth but will try to do so today. in the meantime, can you tell me how to begin the vba training from this website?

[matthewspatrick]

Nicely done, jindon. You may want to have a look that this:

http://vbaexpress.com/kb/getarticle.php?kb_id=841

[jindon]

Patrick,

Thanks for the link and I find it really useful.

I haven't developed the knowledge about Regular Expression and used it
only when I have no other means so far.

I'm now getting better understanding now.

rgds,
jindon

[lucas]

THANKS! I HAVEN'T yet gotten to look at this in depth but will try to do so today. in the meantime, can you tell me how to begin the vba training from this website?

I think you can get started with the training here (http://vbaexpress.com/training/).

I agree with Patrick. Nicely done Jindon

[pdeshazier]

thanks. i'm not exactly able to read all this, as i don't yet know vba. do i just open the spreadsheet and then open vb editor and paste this in?:hi:

[lucas]

once in the vb editor go to the menu at the top and click on insert-module

paste your code in there. Close the vb editor and in excel go to tools-Macro-Macro's and select the routine from the list. If you only added one for instance the sub test provided by jindon above you will see "test" in the window. Select it and click run.

[pdeshazier]

THANKS. another related question: how do i output something from an excel file as an ascii text file, maintaining proper positioning? thanks.

[matthewspatrick]

how do i output something from an excel file as an ascii text file, maintaining proper positioning? thanks.

Depends on what you mean. If you need a delimited file, check this out:

http://vbaexpress.com/kb/getarticle.php?kb_id=805

If you need a fixed-width text file, try saving as a PRN file; that will create a text file whose field widths are defined by the column widths in your worksheet.

[pdeshazier]

i'm not thinking i need a delimited file. the specs for the receiving system are fixed positions; however, in my excel spreadsheet, i only see how to "fix" the length of a column, but the columns contain data for different fields with different formats. for example, here are the specs for the file and batch headers the receiving system needs:
Record 01-File header
From Thru
Field# Description Type Length Pos Pos
1 Record Type N 2 1 2
2 Create Date N 8 3 10
3 File Id AN 35 11 45
4 CR/LF Record Terminator X 2 46 47

05-Batch Header
From Thru
Field# Description Type Length Pos Pos
1 Rec Type N 2 1 2
2 Batch ID AN 30 3 32
3 GL Batch Date N 8 33 40
4 Period End Date N 8 4 48
5 Journal AN 10 49 58
6 CR/LF Record Term. X 2 59 60

Having said that, i'm attaching my test excel file, but see how things in column B would be of varying lenghts given the output file specs, so I can't set a width for an entire column.

[lucas]

You can save it as a csv comma seperated file by using file-save as-CSV
If you want to preserve the appearance you can copy and paste the range into a word doc......? not sure what your after.

Edit: Sorry I didn't see the last two posts before I posted this

[pdeshazier]

ah.. so now that you see what i'm after, any ideas? thanks.