opening multiple similiar fixed width text documents

[riwiseuse]

i have numerous monthly statements that are bizarre fixed width junk with pagination inserted, want to strangle the credit card company IT guys.

but meantime i'm trying to open txt files with the same fixed width choices and then erase the lines of pagination oblec that comes with each change of page on the bill.

is this something i can macro or program, preferring XLM but virtual basic if i must.

for instance can i record a virtual basic macro of opening one and placing the correct fixed column widths and then presage it with an input function requiring me to browse to the file i want to open?

or can i write it from scratch.

thanks
brian

[natefarm]

I'm assuming you mean Visual Basic

Yes, use a recorded macro to initially handle your fixed width import, column formatting, setting column widths, etc. Plan your steps in advance to minimize the garbage code that will be produced (and there will be plenty of cleanup to do). Replace the hard-coded file name in the code with a string variable, and load the variable using the GetOpenFilename method. I'm unclear as to what kind of pagination would appear in a txt file, but include that in the macro as well. I'm sure adjustments will be needed. You may want to post your result code if you need help with cleanup and making it generic.

[riwiseuse]

natefarm,

the pagination stuff is just text that is inserted in headers and footers and, unfortunately, when they made the txt file they just lump that stuff in so every so many records it intervenes. i may have to cull that with a different macro afterwards. it should be more or less the same number of lines and verbiage. i might be able to use a search function in word ahead of time or excel afterwards. i am not a visual basic kind of guy but i can see i might have to learn some. i much prefer XLM because its what i learned first but this operation might be best suited to a recorded macro as you pointed out.

bria

[riwiseuse]

I'm assuming you mean Visual Basic

using the GetOpenFilename method.

Nate (or anyone reading along),
so i tried your suggestion this morning and the recorded macro (first code below) works just fine. unfortunately, i can't find anything that seems to give me a simple syntax for the use of the GetOpenFileName method. Maybe there is a simple reference somewhere but i can't find it. after searching this and numerous other websites the closest i could come is the second set of language below that returns

Run-time error '1004':
Method 'GetOpenFilename' of object '_Application' failed

mind you, i am a complete visual basic neophyte. i have one other time done a recorded macro and then changed a number or something to make it work slightly differently, but i am completely uncoordinated at how and where these macros are saved. in the old days we saved XLM macros to sheets in the document. i can open the language using the editor under tools>macros but i don't really understand the hierarchy of the storage or where it is, in a document, its own document somewhere else . . . and I cannot find any simple explanation of how to call this GetOpenFilename command. what it takes as arguments and whether it actually needs the prefix and I have no idea why one would use the prefix Applications but i found that in an old Microsoft intro to using the command but with no explanation of why. i tried using the command with no prefix and that didn't go anywhere either. then i tried Workbooks as a prefix since that is the prefix to the OpenText. I notice there is a space between OpenText and Filename which does not exist in the GetOpenFilename command but it always seems to be used without a space as you typed it. Is there some kind of glossary of commands that would explain the parameters and syntax. this would be helpful for any commands i try to insert although right now i'm just trying to get a filename call.

thanks, brian

Sub openstatementtext()
'
' openstatementtext Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+I
'
Workbooks.OpenText Filename:= _
"NONAME:Docs:Business:Work:statements:estmts:2012-04-26.txt" _
, Origin:=xlMacintosh, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 3), Array(12, 3), Array(23, 1), Array(71, 1), Array(94, 1), Array(107, 1), _
Array(131, 1), Array(169, 1), Array(171, 1))
ExecuteExcel4Macro "WINDOW.SIZE(398,53,"""")"
ExecuteExcel4Macro "WINDOW.MOVE(2,-42,"""")"
ActiveWindow.SmallScroll Down:=139
End Sub


Sub openstatementtext()
'
' openstatementtext Macro
'
' Keyboard Shortcut: Option+Cmd+Shift+I
myFile = Application.GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText Filename:= _
myFile, _
Origin:=xlMacintosh, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 3), Array(12, 3), Array(23, 1), Array(71, 1), Array(94, 1), Array(107, 1), _
Array(131, 1), Array(169, 1), Array(171, 1))
ExecuteExcel4Macro "WINDOW.SIZE(398,53,"""")"
ExecuteExcel4Macro "WINDOW.MOVE(2,-42,"""")"
ActiveWindow.SmallScroll Down:=139
End Sub

[natefarm]

In future posts, please enclose code in code tags as requested in forum rules.

Below is a 'cleaned up' version of your code that would request a file name and open the file:

Please Login or Register  to view this content.

Press Alt-F11 from Excel to open the VBA window. From the View menu, select Project Explorer to open a tree view of your workbook. If you already have a code module, you'll see it there (probably Module1). If not, from the Insert menu, select Module to create one. That's where you'll normally enter or paste code, such as the above.

To proceed with cleanup of the pagination text, you would need to attach a sample text file. Please obscure any private data that it may contain, but be careful that doing so doesn't affect the layout of the data. For example, change My Name to xxxxxxx.

[riwiseuse]

sorry about the code. can't figure out how to do that. do i select and click one of the glyphics. which one?

and i had forgotten about calling out variables but i pasted your code into my macro and saved it and got the same error message.

it never got as far as any dialogue box to pick a file or anything. as far as i can tell, it isn't happy with the syntax of the Application.GetOpenFilename command but got no idea how to fix it.

thanks