Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column. Do not put blank lines in your spreadsheets, and please do not put more than one kind of information in a column.
Thanks,
GtDS
Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
Fine. Just don't ever let me see it.
A bit frustrating...you might say that. :-)
Well, I'm off to gnash my teeth!!
Posts: 5,094
Threads: 1,252
Joined: Sep 2012
Reputation:
0
I'm nearly there, yes. :-)
Posts: 19,370
Threads: 1,726
Joined: May 2025
Reputation:
3
I just finished working on a project that extracts several thousand records from a spreadsheet, reformats the data, deposits it in another spreadsheet in the correct format, then extracts it again and copies it into a table in an Oracle DB. Then the data is copied from the 'holding' table to multiple tables within the DB and it updates and 'effective date' field. The only glitch is that I haven't been able to figure out how to tell Excel to stop cycling through the data on the first run when it hits empty rows. Instead, it tries to copy 65k+ rows...that takes a bit of time. It then generates some weird errors that have nothing to do with the actual problem (Yeah, Microsoft!) then continues on its merry way. That was a fun project.
Posts: 2,373
Threads: 150
Joined: Oct 2018
instead of blank line I like to use "n/a" unnecessarily....
Posts: 202
Threads: 10
Joined: Mar 2011
Reputation:
0
ztirffritz,
Do not use a "For" loop to process the data in a worksheet. The Rows.Count property on the worksheet will always return 65536 no matter how much data you have in the sheet. Try using a "Do" loop like this instead:
Dim currentRow As Long
currentRow = 1
Do
.... Rest of your stuff here ....
currentRow = currentRow + 1
Loop Until (Excel.ActiveSheet.Cells(currentRow, "A") = "") or (currentRow = 65537)
Posts: 57,816
Threads: 5,861
Joined: May 2025
Reputation:
4
This line intentionally left blank.
Seriously, Greg. Your data is the product of 40+ year old accountants, right ? They never learned that the best way to handle data is.. as if it was a database.
Put together one of those sexy powerpoint presentations and teach them a class.
Remember the early days of DTP ? "The Mac is not a Typewriter" ? We take word processing for granted, but lots of people still use Carriage Returns, and then wonder why the text doesn't flow properly.
It's just education and explanation. They can't help themselves. They never learned to think different.
Posts: 19,370
Threads: 1,726
Joined: May 2025
Reputation:
3
[quote cbelt3]Remember the early days of DTP ? "The Mac is not a Typewriter" ? We take word processing for granted, but lots of people still use Carriage Returns, and then wonder why the text doesn't flow properly.
It's just education and explanation. They can't help themselves. They never learned to think different.
When I first started working for my current employer 10 years ago, my first job was to reformat all of their work instructions as part of an ISO-9000 certification process. I couldn't figure out what the heck was going on with them because I'd add one single space and the entire document would go out of wack and things moved all over the place. The original creator thought that they were works of art, but had apparently never used a word processor before. She had created colored boxes that marked section headings throughout the documents and the the section titles were in the boxes...unless you added a single space anywhere on the document...After much consternation I figured out what was going on...she manually formatted the ENTIRE document as if it were created on a typewriter. Rather than right-click on the pretty boxes that she created and select 'Insert Text' she use the space bar and tab key to get the text into the box. All of the Outline format was manually created using the space bar and tab key. Part of me wanted to re-hire her just so I could fire her again. Part of me felt great sympathy for her because she accomplished her goal, but I can't begin to imagine what a painful process it must have been. I ended up simply re-typing everything...hundreds of pages of documents. That was the moment that the digital-divide became apparent. No wonder the president of the company didn't see the benefit of computers...know one there knew how to use a computer.
I don't put more than one kind of information in a column; that's definitely a no no. 