Why do you need 5m records in a single file? I'm guessing the file will take forever to save, and take even longer to load (if it loads at all). Excel might allow 1m rows, but it's not designed to be abused. — ThunderFrame12 secs ago
Whatever the reason you think you need 5 million rows in Excel is, it's wrong. No Excel worksheet ever needs to work with that much data. You're going to be aggregating it, summarizing it, looking things up, etc. - make the SQL backend do that work, and only give Excel the data it needs. This is a blatant X-Y problem: you're solving a symptom (too much data to fit worksheet) rather than the actual problem at hand (wild guess, you're making some report), making Excel do the job of a database. — Mat's Mug13 secs ago
@FreeMan aye, and that's my downvote. it's giving the OP a way to split their monster query into 3 monster queries, as if OP actually needed a spreadsheet with 5 million rows.
But... OPs happy with the kludge to patch his problem instead of rethinking the problem. Whatever works...
@Mat'sMug - going to have to get with you on your Logger. I'd like to implement it, but I'm a bit stuck. Maybe later this afternoon or tomorrow when you have time to hold a hand.
Private Sub ILogger_Log(ByVal output As String)
Dim fn As Long
fn = FreeFile
Open LOG_FILE_PATH For Append As #fn
Write #fn, output
Close #fn
End Sub
wouldn't that work?
the FileWriter part is just an implementation detail: at the end of the day all you need is to make the ILogger_Log method able to persist the output string to whatever log file you want it to work with
the DefaultLogMessageFormatter should work as-is for that use case
(you'd need a special implementation for, say, a DatabaseLogger)
I have a project where on one sheet there is a Vlookup that references table Foo, and when the macro runs it deletes Foo and re-creates it. Is there a way of (easily) preventing the reference to Foo from breaking?
so you can have formulas that evaluate to names of named ranges, and then use INDIRECT to reference them
e.g. I have a price list with CAD and USD prices, so I can lookup from the UnitPrice_CAD or UnitPrice_USD column based on what currency is selected in Header_CurrencyCode :)
For this particular application I'm not worried about it. Its for a former teammate (now in a different department) and if it is a bit slower for her I'm not worried. That said, its maxed out at about 300 rows, and only a handful of columns so it shouldnt be an issue anyways.
@Mat'sMug Thanks sir. I was stuck on trying to figure my way around TextWriter.CanWrites CanWrite = openedFiles.TryGetValue(fileName, fileNumber) missing TryGetValue from Mat'sMug.Dictionary. I guess just ignoring the TextWriter never occurred to me.
you know - walking through the forest, get a tree in the eye, can't see... yada yada yada...
I've noticed that the Rename dialog box does not default to selecting the OK button when the Enter key is pressed... does anyone else feel that it should?
> In the Rename dialog, there is currently no default selection for the OK/Cancel buttons. Therefore, pressing the Enter key does nothing when the dialog box is active.
The OK button should be the default selection, so that pressing Enter activates the same action as pressing OK.
I still haven't finished all the renovations, there's still a washer+dryer combo in the middle of my dining room.. but the wiring is all ready in the new room, we're finishing the drywall and will probably starting priming for paint this week
I was told to post my code here because it needs work. I made a search page at the beginning of a work book. This page has a drop down that allows you to choose what category to search in, then you type your phrase or keyword into the search box and it finds all of the results. It then takes thos...
@Mat'sMug thinking of you and for your sister. Glad your settled in so to speak.
@ThunderFrame I think you will enjoy presenting the material and give the folks something to consider about how RD will help and expose their faults...
VBA Rubberducking
VBA Rubberducking