Wow! OK, let's see what we can do. Going with your most recent post first:
I tried running the ReadData first pass on the real output. I got an error:
"Run-time error '9': Subscript out of range". When I chose "debug," the code
that was highlighed was Ln 41, Col 1:
Sheets(Sht_Out).Range("A2:Z65000").Clear Contents
Sorry about that, I forgot to tell you that you need to change the sheet names
in the macro to match your actual sheet names.
(I didn't think you REALLY use "What I have" and "What I want"!)
If you notice in the Set_Defaults function, the names of the sheets are defined
as:
Sht_In = "Input"
Sht_Out = "Output"Usually this error "Subscript out of range" means that an
array (or collection) value doesn't exist.
Do you have a sheet called "Output"?
if not, then, in the collection of "Sheets", the array subscript
Sheets("Output") is "out of range".
Regarding the format of the original data: I have three options to create a
text
file. I can create the report directly to a .txt file (Ascii--I don't know
what
that means). I can also "print to screen" to get the report in the internal
document viewer--what I called the PDF-looking file earlier, then export to
either a "text" or "tab-separated text" file. I had been choosing Excel97-2000
from this list.
ASCII stands for "American Standard Code for Information Interchange".
what it basically means is that all these characters you see on your screen
(and
even ones you CANNOT see) are given a number.
The numbers:
66,101,99,107,121 represent "Becky"
while
66,69,37,75,89 represents: "BECKY"
character 13 represents a carriage return and line feed
while (I believe) character number 9 is a "tab" character.
so, basically... an ASCII file is a "text" file, that could be readable by a
simple text editor like Notepad.
I think I understand what you're saying about the underlying data--that
basically the data might be cleaner/more easily malleable if it's not in the
Excel format. Do you have a preference of the three types of outputs above?
The
tab-separated, I'm assuming? I experimented with all three, but I can't send
them out because they're not de-identified. I am going to go ahead and put 3
sample accounts into the system, then generate the report from them. I had
manually changed all the info in the previous attachment after I exported to
Excel.
The variations on the report formats are basically what "delimiters" they use.
some may not use delimiters at all, but simply put spaces in until the columns
line up (not very common any more)
As you suggested, the tab-delimited option is probably best.
Can you export a few records, then just use NotePad to edit it and change the
name(s) to something like: "Paul Criminal"
or even: "Micky Dolenz", "Michael Nesmith", "Peter Tork", and "Davy Jones"
I'd use "John Lennon", "Paul McCartney", "George Harrison", and "Ringo Starr",
but someone might get upset... :o)
I guess a more "realistic" choice would be: "Johnny Cash", "Randy Travis",
"Willie Nelson" (all having done some prison time)
Of course, I might be showing my age here...
Perhaps you'd prefer "Justin Beiber", "Selena Gomez", "Lady Gaga", and "LL Cool
J" ??
That being said, I don't have an easy answer to your question about which
format
I want to use as the "input data." There are 2 considerations:
1. Difficulty and/or time to increase my skills: I would like to learn how to
replicate the principles/coding for 2 other reports with different
information.
However, I don't have a good sense of how long that would take me. I took a VBA
class almost 10 years ago and consider myself relatively computer savvy and a
quick learner, but there are too many unknowns to guess how long it would take
me to hash through things.
2. Difficulty for other users: The final users for this report and the others
mentioned above have ZERO experience with macros and have differing levels of
computer-savviness. Is the process for creating the output significantly
simpler
or less prone to error when starting by text file or starting by Excel file?
They will need to do multiple times per month.
Do you have any thoughts on this?
The question is: How do YOU want the process to work?
What makes it easier on YOU (and the people that will use this in the future)?
Reducing the number of manual steps is vitally important to reducing the
likelihood of problems later.
Is this something that MAY be generated automatically?
For instance: when you "export" the data from the software, is one technique
easier to use than another?
Some programs allow settings to be saved as "defaults" for reports.
Can it be set up so that the report can be run with very few keystrokes?
Who will be using this in the future?
If we do it right, no one will see the VBA code in the future!
Since you've had at least SOME exposure to VBA, we should be able to do very
well.
I'll document each step in the code so that you understand what it's actually
doing.
Also, do you know how to use the VBA "debug" mode to step through the macro one
line at a time?
If not, I'll be glad to walk you through that too.
The other consideration is repeatability.
Some records may not have values in all fields (like the Co-Defendant field)
as long as the output string takes this into account, we'll be fine.
But if one of the formats simply add this "extra" field where another field
existed in a previous record, then it makes it more difficult.
How about sending me a sample file in a tab-delimited format.
You can change the names, (using NotePad)
I'd prefer not to use WordPad, because it actually adds some characters to the
end of each line (usually paragraph symbols)
I don't care about the dates/times or court names.. they'll mean nothing to me
and will be deleted when I'm done.
But if you're concerned, you can change the names to something different too...
like "liverpool", "Boca Raton", "Indianapolis"
I'll await your thoughts on choosing an input. Meanwhile I'll actually enter
the three sample accounts into the software so you can see the data in the
different forms.
Thank you!
On Thu, Dec 6, 2012 at 11:56 AM, Paul Schreiner <schreiner_p...@att.net> wrote:
Becky,
>There are many "clues" here that lead me to suspect some things about the
>original data.
>
>First:
>you said the file is in Excel97-2000 and you're working in 2010...
>many (older) software packages are designed to allow reports to be "exported"
>to text files, or "spreadsheets".
>
>Since they're doing a "simple" export and are not adding any advanced features
>to the file,
>they haven't bothered to change their file format.
>Since everything they're exporting is compatible to the newer versions of
excel,
>there's really no benefit for them to change.
>
>Usually, if there's an option to export to excel97, then there's an option to
>export to a text file.
>(my "personal" preference for this type of work)
>
>I find it odd that you said the sample data was "extracted from a PDF-looking
>report".
>It's actually possible that your Acrobat Reader (PDF) is being used for
>viewing
>.txt files!
>In which case the file itself may have been a text file, and it "looked" like
>a
>PDF file because that's what you were using to view it.
>
>the secondary "odd" part is that in viewing the "PDF-looking report", you (or
>someone) CHOSE to enter data in columns E-F-G..
>Is that where the "real" data would end up?
>in other words: is this sample data "consistent" in formatting as the actual
>data will be?
>
>
>Here's my first pass.
>
>I created a macro called "ReadData"
>
>Now, I made some "assumptions" based on your sample data.
>It may be that when actual data is used, these assumptions don't hold up.
>
>let me know which direction you'd like to go from here!
>
>
>
>
>Paul
>-----------------------------------------
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-----------------------------------------
>
>
>
>
>
________________________________
From: Becky Kaliq <beckyka...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Thu, December 6, 2012 12:09:36 PM
>Subject: Re: $$Excel-Macros$$ Rearrange data: Move and split cells, delete
>irrelevant info
>
>
>
>Paul,
>I'm very excited to find someone who thinks this will be fun! Once I know
>more,
>I'm sure I'll think it's fun, too. :) I'm currently training other staff
>members, but I will answer your questions as soon as possible later today. I
>believe we do have the option of exporting as a text file. What I attached as
>sample data was actually extracted from a PDF-looking report. I'll get back
>to
>you ASAP, and thank you!
>Becky
>
>
>On Thursday, December 6, 2012 9:50:24 AM UTC-6, Paul Schreiner wrote:
>Becky,
>>This looks like FUN (I know, sick AND twisted!)
>>
>>------------------------------ -
>>There's a few things I need to help you out.
>>
>>First of all, do you recieve this data already in an Excel file?
>>or is it a "flat" text file?
>>
>>If it's a "text" file, it would almost be easier to use VBA to read the file
>>itself.
>>I could read each line,
>>for the first row of data looks like it was probably tab-delimeted.
>>Your first field of interest is in column "E"
>>
>>The second line looks like it has two delimeter pairs.
>>each field and value is separated by "--"
>>then, the field and value are separated by ":"
>>
>>although, the first line looks like it SOMETIMES can have a "--"/":" pairing
>>(with the Action Note:)
>>
>>I think I can do this fairly quickly.
>>
>>but first, I'd like to know if THIS format (as an Excel sheet)
>>is how you want to process the input data.
>>
>>Or would you prefer that I/we use a "dialogbox" to select an external file to
>>use as input.
>>
>>
>>
>>Paul
>>------------------------------ -----------
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>------------------------------ -----------
>>
>>
>>
>>
>>
________________________________
From: Becky Kaliq <becky...@gmail.com>
>>To: excel-...@googlegroups.com
>>Sent: Wed, December 5, 2012 4:45:48 PM
>>Subject: $$Excel-Macros$$ Rearrange data: Move and split cells, delete
>>irrelevant info
>>
>>
>>Hello all,
>>I have an Excel output from a legal software in which I need to
>>(significantly)
>>rearrange the data. I’ve attached a sample of the output with identifiers
>>removed. The second sheet in the workbook is my ideal output.
>>
>>Here are the rearrangements I’d like to be able to do:
>>1. Data for one person comes out on two rows; I would like it all on one row.
>>2. Some data are condensed into a single cell; I need particular items
>>extracted
>>to separate cells.
>>3. Output gives irrelevant data; I would like it deleted.
>>4. I would like the remaining columns arranged in a particular order.
>>
>>Notes:
>>-- I am new to macros in Excel and have a little experience writing Visual
>>Basic
>>code for an Access database. I can get to the Developer tab, et cetera. I
>>have
>>recorded a basic macro (cut and paste). I see where to access the code in my
>>PERSONAL.XLSB modules.
>>--Output is created for Excel 97-2000. I am working in 2010.
>>--I left the output formatted exactly as it comes out of the software,
>>including
>>column widths.
>>--Output is sorted by Court with a header row for each. I don’t need those
>>header rows.
>>
>>--Not all defendants will have data for all my desired columns (i.e., there
>>may
>>or may not be a second defendant, a service date, or a type/note). I
>>replicated
>>this with my sample: one case has a second defendant and a service type. A
>>different case has a type/note.
>>--I would like to be able to use the macro(s) weekly when we generate this
>>report. The usual week will have 150-300 cases.
>>
>>Any assistance you all can provide will be much appreciated!--
>>Join official Facebook page of this forum @ https://www.facebook.com/
>>discussexcel
>>
>>FORUM RULES
>>
>>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not
>>get
>>quick attention or may not be answered.
>>2) Don't post a question in the thread of another member.
>>3) Don't post questions regarding breaking or bypassing any security measure.
>>4) Acknowledge the responses you receive, good or bad.
>>5) Jobs posting is not allowed.
>>6) Sharing copyrighted material and their links is not allowed.
>>
>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>members
>>are not responsible for any loss.
>>---
>>You received this message because you are subscribed to the Google Groups "MS
>>EXCEL AND VBA MACROS" group.
>>To post to this group, send email to excel-...@googlegroups.com.
>>To unsubscribe from this group, send email to excel-macros...@
>googlegroups.com.
>>Visit this group at http://groups.google.com/ group/excel-macros?hl=en.
>>
>>
>>--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>--
>Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members
>are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To post to this group, send email to excel-macros@googlegroups.com.
>To unsubscribe from this group, send email to
>excel-macros+unsubscr...@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros?hl=en.
>
>
>
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
