Paul,
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
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.
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.
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?
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 <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<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.
