Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Hopefully people still read this, as I had an excel question and no where to put it. However, it's not quite an implementation question, but rather a performance one.

Do any of you guys know where Excel 2003 bottlenecks performance wise?

I've got a user who is working with an 80mb very complex spreadsheet in Excel 2003 on Windows XP SP3 32x. First of all, I realize '80mb spreadsheet' is already throwing any kind of objective performance out the window, but I was hoping to minimize the impact. Right now when he changes something and has to recalc it literally takes close to 2 minutes for the entire sheet to update. A lot of this is obviously due to the complexity of the sheet, but I was wondering if I could minimize the slowdown as much as I can with the resources I have. So what I'm wondering where the possible slowdowns could be.

I've already upgraded him to 4gb RAM from 2gb, which hasn't had too great an effect. I'm thinking of one (or all) of these lines of attack, and am wondering which would be the most 'bang for buck' in your guys' opinion. This is in order of expensiveness/severity in my opinion:
1. Upgrade him to Windows 7 32x (in case it's XP that's the major chokepoint)
2. Upgrade him to office 2010 (in case it's office 2003)
3. Give him a new 64x processor machine and put Windows 7 64x on it (in case it's 32 bitness)
4. Slip booze into his coffee every day so everything seems faster

I also realize that I could do 1-3 and it'll have no effect at all, which is why I'd like to know what you guys feel the 'real' bottleneck might be. Is it just that no spreadsheet of that size will ever be efficient and it can't be fixed? Or one of 1-4 above might do the job?

# ¿ Feb 9, 2011 23:43

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 16:33

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Thanks for your reply. I will say there are a lot of pivot tables involved (e.g. a formula in one cell might refer to 3 of them, multiplied by say 60 other cells that are summarizing/adding up). The impression I get is that I should be focusing on tweaking the sheet instead of improving the profile of the machine running it, which is probably a wise idea. Unfortunately I'm not too 'with' Excel so we'll have to see what fruit that bears. A quick google around shows me quite a few suggested pages on optimizing performance for sheets.

# ¿ Feb 10, 2011 00:13

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Welp, I took the easy way out and just updated the machine to Windows 7 x64 w/ 4gb of RAM and that seems to have taken care of it. Takes about 1/6 the time now to sum up, and I think that's mostly network related from ODBC hookups. Thanks for the advice guys, and I'll keep it mind, but in the end it was just easier for me to get him a new machine than it was to comb through the formulas on the sheet.

# ¿ Feb 24, 2011 00:41

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I also use COUNTIF for similar operations since it can serve the same purpose, but also lets you know if there's duplicates.

The way COUNTIF works is it'll count each occurrence in the range. If all values are unique you'll get a '1' in your derived column instead of the original value (like VLOOKUP provides). If the email occurs twice you'd get a 2, etc. If there's no match it'll be 0 instead #ERROR. The similar syntax to what was provided above is:

COUNTIF(A:A,B1)

It works in reverse, first the range then the value. It's not exactly what you wanted (takes a bit more work to get the values) but it can be a handy function.

# ¿ Mar 30, 2011 00:23

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

angry armadillo posted:

I couldn't see a vba thread so I am going to assume I am in the right place. If not tell me to go away

Is it possible for a macro to get the free space available on a network share - one of my duties is a daily check of all servers including hard drive space - I was wondering if a macro could fill this in by getting free space from the C$ and D$ (etc) shares

Any help or advise would be appreciated. Thanks.

First google result for 'excel vba network drive size'
http://www.codeforexcelandoutlook.com/vba/using-wmi-services-in-vba/drive-information-local-network-mapped-drives/

There's probably a hell of a bunch of auth stuff involved but since you're using VBA for file access I assume you don't care about running it in admin context.

# ¿ Apr 5, 2011 00:24

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

That'll work, but if you wanted to include the empty string <>0 might work too. If you don't want to deal with the string the best way would be to clean the data and ensure it's all numeric, or even do CONVERT on the seek range.

# ¿ Apr 11, 2011 19:40

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Goodpancakes posted:

Well I ended up with quite the excel problem.

I have a rather large database (~4000 data points). These points include location data in the form of Latitude and Longitude. In a good clean copy this data is split between two columns. One would read Latitude: 40.5633 and the other Longitude: -124.3533.

Now the ugly database I have to clean up has this data dumped as one string into one column, such as 70227N148316W. Is there an easy way to split this data up? Or is this an old fashion grind-fest of Ecel: The Korean MMO?

Sorry I don't understand lat/long very well; would the first value always end in N and the second always end in W? If so that's pretty straight forward. Couldn't you just do a search and replace on 'N' and replace it with 'N,' and then save the resulting file as a CSV?

# ¿ May 13, 2011 00:15

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Goodpancakes posted:

This is certainly a great idea, and I feel a bit daft for not thinking of it (yes each one will end with N or W). My question is, then, since the file contains a lot of other information this would replace all Ns with "N " yes? Can this be done by column?

I just tested it and if you highlight just one column (e.g. by clicking the column head) and then do the replace it only worked within that column.

# ¿ May 13, 2011 00:39

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Yeah, you could just keep a formula on rows A1-A6, and then enter your payment on B1-B6. The formula would be something easy like =A1-B1, =A2-B2, etc.

# ¿ Jun 10, 2011 18:52

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Lady Gaza posted:

Yeah that's what I've been doing. Only problem is that it takes aaaaaages as genetic codes are quite long. Oh well! Thanks anyway

Only way you're going to get around it is with VBA (which would be pretty straightforward String.Replace operation) or maybe a macro solution like AutoIT.

You could also do more with the file than just copy+paste though, I believe you could save out each file as TSV/CSV and then run an offline regex editor over it. E.g. textpad, in some kind of batch operation. Depends on how necessary the user interactive copy/paste aspect is.

# ¿ Jul 25, 2011 21:27

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I see what you're saying, and it's certainly possible. I think you'd have normalize/rationalize all your spreadsheet data first though, so you don't have the same redundancies in whatever database you end up with. Maybe check out these links:
http://en.wikipedia.org/wiki/Database_normalization
http://www.databasedev.co.uk/database_normalization_basics.html

Once the sheets are de-duped it'll probably be way easier for you to proceed; I think that you're still at the organization part of this project, and it's premature to say if you'll be using Access/SQL/etc.

That said, I suggest you actually go with some accounting package at this point because while you will be able to fake out a lot of the functionality, at some point you're likely going to need a 'real' system anyway. It depends on the size of the organization but the 'best' solution organizationally is probably to start from scratch. Here's some packages based on scale that I am in no way endorsing, but have noticed most businesses using at the various sizes:
- SimplyAccounting (small)
- QuickBooks/Pro (small/medium)
- Microsoft Dynamics/Great Plains (Medium/Large/Possibly very large if you hate yourself)

# ¿ Aug 11, 2011 18:37

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

uG posted:

Well my Perl script is creating a bunch of csvs, from which i'm trying to group some of them together in a workbook as worksheets with a secondary script. I'm working with multiple large files here that are located on a *nix server, so i'm preferring to do this without windows/excel (so I don't have to keep downloading/uploading). I can do all of this with Perl, except it runs out of memory very early on due to the way Perl handles memory. I briefly looked at Python and PHP, and it seems they either couldn't write more than 65536 rows or had memory limitations.

I can do this manually in excel by opening all the csv files and dragging their sheet tab to another sheet, and saving as xls. But i'm trying to avoid that so I don't have to download the files to a windows machine, then reupload them to the *nix server.

If you're doing straight ascii CSV you might be able to do it in batches (with distinct entry/exit so memory gets freshed) and then just concatenate the files together into one mega CSV. What's the source, is it a database?

# ¿ Aug 31, 2011 19:07

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Kind of an odd question, when saving a worksheet I got this warning today:
"Privacy warning: This document contains macros, ActiveX control, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the "Remove personal information from file properties on save" option on the Security tab of the Options dialog box on the Tools menu."

What's weird is that I'm the only one who edits this sheet, and all of my edits are entirely textual (it's actually just a central place where we keep intranet URLs and stuff like that). Is there any way to get a list of the aforementioned xml/activex/macros etc. that would be causing the error? It's Excel 2003 if that makes a difference.

# ¿ Sep 26, 2011 21:42

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

This is more of a 'wouldn't it be nice' kind of thing. I've got a lady I'm helping out who is doing pay per click campaigns. I've pulled a bunch of stuff out of Google Keywords via API into a database, and then given her a spreadsheet. There's a unique ID, a company name, and a keyword. These may be repeated as there could be multiple keywords per uniqueid/keyword. What she does is decide which keyword is 'best' by putting an 'x' into a column. So what she ends up with is a sheet that looks like:

code:

ID  |  Name     | Keyword      | Selected |
10  | Joe's Art | Joe's Art    | NULL     |
10  | Joe's Art | Art New York | X        |
12  | AA SexToyz| AA SexToyz   | NULL     |
12  | AA SexToyz| SexToyz NY   | X        |
12  | AA SexToyz| Butt Plugs   | NULL     |

The problem arises is that she's sending me back the sheets to 'double check' that:
a: There is at least one 'x' per uniqueID
b: There is not more than one 'x' per uniqueID
c: A list of all 'selected' rows, with the NULL selected rows removed

This is incredibly easy for me in my SQL database, which I accomplish like so:

code:

Case a:
SELECT * FROM Keywords_temp WHERE Selected IS NULL AND UniqueID NOT IN
(SELECT UniqueID FROM Keywords_temp where Selected='x')

Case b:
SELECT UniqueID from Keywords_temp WHERE Selected='x' 
GROUP BY UniqueID Having COUNT(*)>1

Case c:
SELECT * FROM Keywords_temp WHERE Selected='x' AND
UniqueID NOT IN
(SELECT UniqueID from Keywords_temp WHERE Selected='x' 
 GROUP BY UniqueID 
 HAVING COUNT(*)>1)

The problem being, all this data is in the sheet I sent her, but I'm trying to think of an easy way that she can formula up the same results without having to come back to me. The initial dataset was provided as part of a contract, but all this post hoc support was not. The sheet has about 50,000 rows. Does anyone know of an Excel solution that would let me do what I'm doing in SQL above? You don't have to make a full blown example, I'm pretty experienced in these things.

EDIT-Also, she can't connect to my DB directly via ODBC or anything like that. She's using Excel 2010.

# ¿ May 25, 2012 01:55

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Thanks, I can see how that would work. I'd been stuck in the 2003 world so long the only thing I could think of would have been tonnes of countifs and named ranges and so on, and just didn't want to go down that road.

# ¿ May 28, 2012 01:03

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

This person is pretty... not smart with Excel (though great at online marketing) so I didn't want to complexify things with allow scripts to run/popups etc. I like the elegance of the pivot as well, it worked out fine.

# ¿ May 31, 2012 02:44

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I'm not sure how I lived before I knew Google Refine existed.

# ¿ Jun 22, 2012 01:33

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

By range do you mean named range (e.g. 'results') or cell range (e.g. A1:A500)? If the latter, unfortunately COUNTIF only accepts ranges (or one individual cell) as an argument. You can kind of get around it though if you can clearly identify the cells you don't want counted:
http://www.pcreview.co.uk/forums/using-countif-specific-cells-t2532815.html

What they're basically doing is one COUNTIF range subtracted from the other; I'm not sure if this would help you, or if you just need to be able to arbitrarily count specific cells (e.g. COUNTIF(A2 Q11 Z23,">0")).

# ¿ Jun 26, 2012 23:57

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I used to know how to do this, but I've been stuck in customer facing land for too long lately. I've been given a longish sheet of products/services, that have quantities, and prices:

code:

Service |  Qty  |  Price
Anvil |  5 |  $50
Lamborghini | 2 |  $500000
Anvil | 4 | $55

I have no idea what the Services are, except they are consistent (e.g. Anvil is always Anvil, not sometimes Anvilz). For every unique service, I want to sum up both the Qty and the Price.

In database parlance it'd be pretty easy, something like:

code:

SELECT Service,SUM(Qty),SUM(Price)
    FROM BilledService
GROUP BY Service

But I'm not sure how to begin with a spreadsheet.

# ¿ Nov 3, 2016 20:00

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Hey thanks for that quick reply. Unfortunately I was too optimistic about the integrity of the data, and it's going into a temp database after all because crap is all over the place on this one (for example I was guaranteed that Price wouldn't change over time. Guess what??? It changes over time.)

# ¿ Nov 3, 2016 20:44

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

This is an odd question and I'm not sure if it goes here, but I'll give it a whack.

I want to generate and print barcodes for internal inventory/scan purposes. I know how to do this in Excel/Word, but I've transitioned the office to LibreOffice Calc/Writer. So far from searching all I can find are some obscure VBA/add-ons from 2012, and was wondering if anyone knew a "known good" solution that doesn't involve running code locally (e.g. in the past I wrote a quick and dirty .NET program to do this) and can Just Work in LibreOffice? What I would need to do is create arbitrary bar codes and print them basically.

EDIT-Sorry, left one thing out. I tried all the barcode extensions/add-ons in the LibreOffice library and none of them work; they all error out with Python/Java errors during the install process.

Scaramouche fucked around with this message at 20:56 on Dec 27, 2016

# ¿ Dec 27, 2016 20:35

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Hmm, double posting with a workaround for now.

There's a plugin for Paint.net that generates UPC-A that I'm using for now:
http://forums.getpaint.net/index.php?/topic/31559-barcode-v14-nov-14-2015/

This isn't what I'd prefer (because it generates bitmaps) but is enough to get me started for now. If anyone has a better solution please chime in!

# ¿ Dec 27, 2016 21:51

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Sad Panda posted:

First time ever playing with a VBA macro.

I found the following code to change the colour of a tab based on the contents of a cell.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target <> Range("A1") Then Exit Sub
    With Me.Tab
        Select Case Range("A1")
            Case 1: .Color = vbBlack
            Case 2: .Color = vbRed
            Case 3: .Color = vbGreen
            Case 4: .Color = vbYellow
            Case 5: .Color = vbBlue
            Case 6: .Color = vbMagenta
            Case 7: .Color = vbCyan
            Case 8: .Color = vbWhite
            Case Else: .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub
It's not quite what I want so I tried making a simple bit of code based on that to start as a building block to teaching myself VBA, and came up with the following. But whether the number 5 is in K26 or not the tab never changes to black.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
        With Me.Tab
            If Range("K26") = "5" Then
                .Color = vbBlack
            End If
        End With
End Sub
What am I doing wrong?

My goal is basically to look at 3 cells and change the colour of the tab based on that. Something along the lines of..
code:
If Range("J52") = "Yes" Then .Color = vbBlack  ' if that's the case then that's the end and it shouldn't change colour based on the others. This is the priority
Else If Range("M50") = "Yes" Then .Color = vbRed
Else if Range("M51") = "Yes" Then .Color = vbGreen
Something along those lines is what I'm after. Only one of M50 or M51 can possibly say yes at the same time.

Thank you so much.

Phone posting and not a VBA expert, but aren't you forcing a string comparison by using ="5" instead of =5?

# ¿ Feb 17, 2017 00:14

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Eh, indexes are a database thing mostly, and VBA is definitely not a database.

# ¿ Feb 22, 2017 09:13

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

If that gets too weird, you might be able to write a PowerShell script that'll output XLS, it'll be faster on the file access side.

# ¿ Feb 28, 2017 00:45

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I'm not going to delve into a snarl of Excel that I don't understand, but I will say that what you are tasked with duplicating sounds like an enterprise Time & Work management application. The "real" solutions out there are pretty expensive, and either SaaS or server-based applications. Depending on your organizations size, it's kind of crazy that they're trying to do it with a jumped up Excel spreadsheet. This sounds like it is pretty important organizationally, and I don't know if you're able, but I'd suggest replacing the sheet with a real database app at the least that can scale/expand as needed instead of cobbling together a bunch of VBA that you/everyone involved will grow to hate. I realize how unhelpful that sounds, but personally this sounds like something way out of scope for Excel to me.

# ¿ Apr 5, 2017 19:06

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

duh too many tabs open

# ¿ Jun 12, 2017 17:35

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I haven't tried it, but I made a note to myself to try it a while ago, but that might work with Awesome Table. If you do try it let me know so that way I don't have to try it.

# ¿ Jun 15, 2017 23:49

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I think in a larger sense too, it's kind of weird to have a web app generate what are essentially dead binary files, but that's more an organization issue around use of technology.

# ¿ Jul 25, 2017 18:34

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

In cases like that where you're fighting with the formatting code I usually just export to CSV and regex to clean it up.

I think you'd need to find (assuming POSIX and CSV):

code:

,(0-9+)/(0-9+)/(?:0-9+),

and then replace

code:

,\1/\2,

Alternatively you could replace the / with a tab or comma so that when you re-open the file each month/day will be in it's own column

code:

,\1,\2,

(commas left in to prevent over eager matching over/outside of column bounds)

# ¿ Nov 1, 2017 22:13

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

RegEx is like Find + Replace on steroids. You can match various patterns, replace them with static strings, or replace them with parts of other text. It is hella powerful and cool but also dangerous if you search for the wrong thing.

I'll not learn you the whole thing but I'll explain what I said more. First you need a regex capable text editor; I use Textpad but stuff like Ultapad or Notepad++ have it as well.

The first two things I said to search for was (the slash is the date separator and not a regex thing):
(0-9+)/(0-9+)/

In regex talk that means any number from 0-9, and the plus sign means match multiple numbers. So 1111 would be found, 11a11 would only find the first 11, and zyxqb would not be found at all. The brackets around the number mean I want to "remember" what was found so I can work with it later. They're assigned in order. The third thing was this:
(?:0-9+)

The ?: means match on this, but don't "remember" it, because this is the year and we're discarding it anyway.

That's the "find" part, now onto the replace:
\1,\2

So the things "remembered" are automatically assigned a number so you can reference them later. \1 is the Month and \2 is the Day. So theoretically (I'm doing this from memory) the result from this set of data:

code:

11/12/2019
01/01/2001
6/15/1979

Would be

code:

11,12
01,01
6,15

Which, in a Comma Separated Value file (CSV) would mean that the month and day now each have their own column when opened in Excel/Calc etc.

So the dangers here are obvious, though the way I've structured it (looking for the slash, including the commas at the end of the column in the original example) mitigate that somewhat. But if you included another column full of dates in the file, this would find/modify those ones as well. Generally if I'm not doing multi-line stuff I usually only copy out the sorted relevant column and paste it back into the file I'm done. There's all kinds of cool syntax to find things in regex like A-Za-z (all upper and lower case letters) \s (space) \t (tab, useful for TSV) \n (newline, dangerous if line-ending religion isn't known) and many more.

# ¿ Nov 3, 2017 18:11

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

mystes posted:

Why does Excel not support regexes in find/replace even though word does?

I haven't used actual Excel in years (luckily VBA doesn't enter my world very often). But LibreOffice Calc supports it!

https://help.libreoffice.org/Common/List_of_Regular_Expressions

# ¿ Nov 15, 2017 19:39

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Busy Bee

You probably already know them, but when I was doing Excel heavy work things like VLOOKUP, SUMIF, and COUNTIF were basically my bread and butter, plus understanding how the graphs generate, named ranges, etc. that you've already mentioned.

# ¿ Dec 1, 2017 21:12

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

That's some good work Lou (fosborb)

# ¿ Dec 4, 2017 20:40

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Yeah I once turned an entirely manual order processing system into a push button solution in a similar way (albeit by moving it into a database and not using Excel magic). The amazing thing is how long people labour under these bizarre conditions and just a bit of knowledge of macros, VBA, RegEx, and data normalization could make it so easy.

# ¿ Dec 7, 2017 21:18

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Not sure where else to put this but thought I'd pick some brains here. We have a web app that generates XLS files; however the resulting file actually looks like HTML like so:

code:

(empty newline)
<table>
 <thead>
  <tr>
  etc etc

Excel 2016/365 can open these (after a warning about file extension not matching file type), but you upload it to Google Docs, and it makes a sheet, but the sheet just shows the raw HTML as above and not the formatted data.

Doing some reading it seems this is an XHTML format from Excel 2003 onward; is there a way to make Google Sheets parse it properly?

# ¿ Oct 4, 2018 19:18

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

mystes posted:

Office supports its own weird/poorly documented html format, and it will open these files even if they are incorrectly labelled as being normal office files. As a result, a lot of websites generate documents in this format simply because it's easier then generating real ooxml files, but it's unlikely that other programs that expect real ooxml files will be able to read them because it's a completely different format.

If they use any excel specific features, you will probably need to use Excel to convert them to real xlsx files.

That said, the part you have included looks like fairly normal html so you could try changing the extension to html; if Google Docs supports reading normal html files it might work (however anything that uses Excel-specific attributes/styles starting with "mso-" will presumably not work), or you could try opening the files in a web browser and copying the table into google docs.

In reviewing it further, there's no actual Excel specific functionality present. You are correct in that these are server-generated and don't ever properly touch Excel except maybe via a DLL during the export process. My guess is that the developer is literally taking HTML output, streaming it out, and putting .XLS on the end of the file.

Renaming it to HTML is proper useless; Google Docs treats it as a Word equivalent file instead of a spreadsheet for some reason.

The weird thing is, the functionality is built right into Sheets via the ImportHTML/ImportXML function, but Google refuses to do it on upload/conversion. I haven't done it but I'm 99% sure if I made an empty Google Sheet and put "=IMPORTHTML("path_to_html_file",table,1)" it would actually work, but these files are disseminated to clients as is, and the majority of them don't even use Excel and are going straight to Google Sheets.

There is some CSS info on the HTML, but it's not weird mso- specific stuff, just things like:

code:

<table class="ReportResultsTable">
                                <thead>
                                    <tr class="ReportHeaderRow">
                                        <th>Product</th>
                                        <th>SKU</th>
                                        <th>Description</th>
                                        <span id="CategoryResultsHeader"><span>
                                                <th>
                                                    Warehouse Name
                                                </th>
                                            </span></span>
                                        <th>Total</th>
                                    </tr>
                                </thead>
                                <tbody>

Frustrating. I can't easily control the output parameters for the "xls" file that is created so I'm looking at trying to fix it on the client/Google Sheets side, but it's looking like that's not possible.

# ¿ Oct 4, 2018 20:34

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

mystes posted:

Yeah I don't know what you can do if you have no control over the web application, Google Docs won't open the files without extra work, and you need it to be possible to open the files as-is in Google Docs.

If you really need this to work, you might unfortunately have to start thinking about something stupid like injecting javascript code into the page.

Hah ha! Weird thing I figured out by testing. If I put this line at the beginning:

code:

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

And this line obviously at the end:

code:

</html>

Google Sheets parses it properly. I'm not even replacing an existing <html> tag because there just never was one there in the first place.

But then I'm thinking; wow Google went out of their way to respect that MS schema? That seems weird... I wonder if...

Turns out this will make Google Sheets read it as well:

code:

<html>
(everything else)
</html>

All it needed was an a valid enclosing <html> block. Ima get on the phone with someone I think. :dogbutton:

# ¿ Oct 4, 2018 21:35

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Remora posted:

Hey excel thread - is there any way to export the formatting of a .xlsx to VBA code (".Range("A5").Font.Bold = True" etc)? Basically I have an intricately formatted report that I am automating in Access, but manually specifying every single piece of this spreadsheet will take hours - any way to automate that process would be incredibly helpful.

It might not be exactly what you want (I have no idea on that, not a VBA user) but couldn't you do the HTML format to at least get font/colour/etc information and then do something with that? I could see regex being helpful there.

# ¿ Dec 6, 2018 22:24

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 16:33

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Harminoff posted:

I've been learning vba during my free time at work over the last year to automate some tasks and now a few other teams learned that I know vba so now I'm just coding vba all day, with the goal of automating the formatting of about 20 different workbooks.

The formatting used to take a few hours to do, and now it takes minutes at most.

I should ask for some sort of raise right? My biggest concern is that if any of them been I'm the only one that would be able to fix it. Job security I guess.

This is going to sound like a jerk-rear end thing to say but sure ask for a raise. But also look to either get out or get rid of the workbooks entirely with a real solution, because in my experience businesses that rely on using VBA to make Excel usable are eternal trash fires of organization.

# ¿ Jun 10, 2019 18:46

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread