Loops that write specific data to file

M

MeiR_ct

New Member
Joined
Feb 22, 2009
Messages
43
Hi all.
I'm trying to create detailed report pages based on excel data.

Here's a little view to the sheet:
2q8dpoi.jpg

I've also attaced this sample if you want to work on it.

First of all, I want the method to "remember" some coulmns by their titles (they'll be always the same), and "know" where to find the needed data.
I guess it'll be several loops that search for "title" and strore the column indexes in variables. ("Business Name" into BusNameCol, "Sign Content" into SignContentCol, etc.)

Now, I want a loop that runs on "Property ID" column (its index will be stored in PropIDCol).
When the loop finds a "new" (not yet handled) value, it will:


  • Create a new file, named as the property id value.
  • Print to file, *only once*, some values like businnes name, owner name, etc. (their column indexes are stored)
  • Print in a new line some other values, like sign content, width, height, etc. (their column indexes are stored)
  • Loop on the rest of lines of the current handled "Property ID", and continue creating new lines for those same other values.
>>> NOTICE:
Same property ids are not all in sequence! So it's not a simple "while" loop.
It'll need to "collect" from the close range (let's say 50 lines).​
  • After that, it'll return to the "main" loop and move to the next "new" property id.

For the method to know where to "return" to, my idea is to have a "check" column (its index in CheckCol). For every line the method handles, it'll put "1" in this column.
It also will be good if I want to run the macro several times (if businesses are added in future), and not to have multiple report files.


There is also line 12 in the picture, which doesn't have property and owner data, I think that in this case it will collect according to business name instead of property id.

-------------------------------------------------------
OUTPUT EXAMPLE:

BusinessName: Paz | Owner ID: 510216054 | Property ID: 166143000003 | Owner Name: Paz Company Ltd. | Owner Address: P.O.B. 222 City

<table class="MsoNormalTable" style="width: 445pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="593"><tbody><tr style="height: 12.75pt;"><td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="148"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5C-A290%7E1.3DL%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:right; mso-pagination:widow-orphan; direction:rtl; unicode-bidi:embed; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"טבלה רגילה"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> <table class="MsoNormalTable" style="width: 445pt; margin-left: 5.15pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="593"> <tbody><tr style="height: 12.75pt;"> <td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="148">
Sign Content<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="64">
Width (cm)<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="64">
Height (cm)<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="64">
Rounded Area<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="73">
Sign Address - Street<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="73">
Sign Address - House<o:p></o:p>​
</td> <td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" width="107">
Sign Location<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="148">
Tasty Yellow<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
250<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
230<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
6<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
rd. 434<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
21<o:p></o:p>​
</td> <td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="107">
Display Window<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="148">
Paz (changing ads)<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
90<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
60<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
rd. 434<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
21<o:p></o:p>​
</td> <td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="107">
Bulding Wall<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="148">
Paz (changing ads)<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
90<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
60<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
rd. 434<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
21<o:p></o:p>​
</td> <td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="107">
Bulding Wall<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="148">
Price List<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
120<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
60<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
rd. 434<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
21<o:p></o:p>​
</td> <td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="107">
Pole<o:p></o:p>​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 111pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="148">
Paz<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
80<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
60<o:p></o:p>​
</td> <td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
1<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
rd. 434<o:p></o:p>​
</td> <td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
23<o:p></o:p>​
</td> <td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="107">
Gas Station<o:p></o:p>​
</td> </tr> </tbody></table> </td><td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td><td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td><td style="width: 48pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="64">
</td><td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
</td><td style="width: 55pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="73">
</td><td style="width: 80pt; padding: 0cm 5.4pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="107">
</td></tr></tbody></table>-------------------------------------------------------
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
You may not need loops to find your header columns.

Consider the following

Code: 
BusNameCol = rows(1).find(what:="Business Name").column
HTH
 
Upvote 0
Oops, I didn't attach the sample file.
And now that I try to attach to a reply, I see I cannot do this anyway.
"You may not post attachments"

If someone want it I'll upload.

And the fool me forgot to say the most important thing:
>>> Thanks *A LOT* in advance for all the helpers!!! :) <<<
 
Last edited:
Upvote 0
Code: 
When the loop finds a "new" (not yet handled) value
I've probably missed something really obvious, but how do we define 'not yet handled'?
 
Upvote 0
Yeah, u missed the end of my post :)

For the method to know where to "return" to, my idea is to have a "check" column (its index in CheckCol). For every line the method handles, it'll put "1" in this column.
It also will be good if I want to run the macro several times (if businesses are added in future), and not to have multiple report files.
Click to expand...

I also don't mind if u suggest some better way, like an array variable that will store every handled property id.
My idea requires more looping, which is, of course, less efficient.
But for future handling, I think there must be some storing in the excel workbook itself.
 
Upvote 0
Ah, and about the actual definition of "not yet handled", I mean a property id which has no its own report file yet.

When I think of it, maybe I can store in another sheet's A1 cell, the path for creating the files, and then the macro will check by filename if the file exists.
If it does, the macro will prompt a question whether to replace the file or go on.
 
Upvote 0
I can try and write some code (from the little clue I have about vba), hoping that someone will fix it into correct syntax...
Code: 
For x = 1 To LAST_CELL ' I assume there`s some way to run until last used bottom cell
     
    If cell(x, CheckCol).value = "1" Then Goto Next ' This line was handled already
     
     
    CurrentProp = cell(x, PropertyCol).value 
    CREATE_FILE CurrentProp + ".txt" ' Replace with the correct command
     
    print CurrentProp + ".txt", "Business Name:" cell(i, BusNameCol).value "| Owner ID:" cell(i, OwnerIDCol).value _ 
    "| Owner name:" cell(i, OwnerNameCol).value "| Owner Address:" cell(i, OwnerAddressCol).value 
    print CurrentProp + ".txt", vbCrlf 
    print CurrentProp + ".txt", " " 
    print CurrentProp + ".txt", "(Sign Content | Width (cm) | Height (cm) | Rounded Area | Sign Address - Street | Sign Address - House | Sign  Location)" 
     
    cell(x, CheckCol).value = "1" ' Put "1" near every handled line, in "check" column. Or maybe should it be:  cells(x, CheckCol) = 1 ?
     
    i = x + 1 
    NextRange = i + 200 
    While i < NextRange Do ' I'm still not sure if to scan and "collect" from close range or the whole sheet
         
        If cell(i, PropertyCol).value = CurrentProp Then 
             
            print CurrentProp + ".txt", cell(i, SignContentCol).value "|" cell(i, WidthCol).value "|" cell(i, HeightCol).value _ 
            "|" cell(i, AreaCol).value "|" cell(i, SignStreetCol).value "|" cell(i, SignHouseCol).value "|" cell(i, SignLocationCol).value 
             
            NextRange = i + 200 ' As long as u find another match, run on next 200 lines
            cell(i, CheckCol).value = 1 ' Put "1" near every handled line, in "check" column
             
        End If 
         
        i = i + 1 
    Loop 
     
Next: ' For the goto above
Next x
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
D
  • Question
How to output a dynamic range where the height of the range varies
Replies
0
Views
1K
dagda13
D
A
  • Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A
I
  • Question
Copy files from one folder to another based on a list in Excel
Replies
1
Views
2K
Fluff
Fluff
A
  • Question
Move rows to sheet based if row contains certain string value
2
Replies
10
Views
1K
aaelex
A

Forum statistics

Threads
1,216,520
Messages
6,131,135
Members
449,626
Latest member
Stormythebandit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top