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

Baxta: Feb 18, 2004; Needs More Pirate

Hi guys,

Im currently reading and parsing an HTTPrequest and inserting it into a worksheet via the following the code.

code:


Set json = lib.parse(HttpReq.ResponseText)
    For x = 1 To json("results").Count
        For y = 1 To lastColumn
            Sheet11.Cells(x + 2, y) = json("results")(x)("" & Sheet11.Cells(2, y) & "")
        Next y
    Next x

Unfortunately this takes a very long time. I have been trying to load the parsed values into an array and paste it into a range but I cant wrap my head around it.

Here is the output I get (which is fine) but I would like it to not go through a loop populating.

Here is what im trying and failing at. It just spits it all into A3.

Am I storing it wrong? How the hell do I get it to just spit it out over the range?!

code:

Dim output As String
    Set json = lib.parse(HttpReq.ResponseText)
    For x = 1 To json("results").Count 'json("result_overview")("total")
        If (json("results")(x)("quantity") <> 0) Then
            For y = 1 To lastColumn
                 output = output & json("results")(x)("" & Sheet11.Cells(2, y) & "") & vbTab
            Next y
            output = output & vbNewLine
        End If
    Next x
    
    Dim MyObj As New MSForms.DataObject
    MyObj.SetText output
    MyObj.PutInClipboard
    MyObj.GetFromClipboard
    'Debug.Print DataObj.GetText
    
    Sheet11.Activate
    Sheet11.Cells(3, 1).Select
    ActiveSheet.Paste Destination:=ActiveSheet.Range("A3")

# ¿ Aug 2, 2011 07:30

Adbot: ADBOT LOVES YOU

# ¿ May 14, 2024 18:34

Baxta: Feb 18, 2004; Needs More Pirate

gwar3k1 posted:

ActiveSheet.Paste should be sufficient with you selecting Sheet11.Cells(3,1) before hand. I've never had to use Destination:=... before. Its possibly that.

Should you expect Excel to split vbTab and vbNewLines into columns and rows? You're making a formatted string which I assume would just paste into a single cell as the string you defined (but I've not used clipboard either).

Consider your first method, but put Application.ScreenUpdating = false at the start of your routine and true at the end. Screen updating is often the slowest part of a massive loop, I've found.

Activesheet.paste just puts it all into A3 however vbnewline enables it to actually go down the right number of rows just all the row data ends up in the one column ( it won't paste as recognizing the vbtab should be forcing it to spam the rows like in my output for the for loop.

Unfortunately, it is 1 billion too slow to populate the entire thing using the for loop as the populator even with screen updating off ( this has to go through thousands of records ). Before you tell me this shouldnt be done in excel I know but I do what I'm contracted to

Desperately need help with this one

# ¿ Aug 2, 2011 11:49

Baxta: Feb 18, 2004; Needs More Pirate

gwar3k1 posted:

How about this:

Paste it to a text file, save.
Open as tab delimited, copy
Paste in required workbook, close & delete text file

Hmm I'll see how that affects performance. Wish I could just paste tab delimited.
I'm sure it's possible as when I'm debugging, the string is saving it with the tabs.

# ¿ Aug 2, 2011 22:06

Baxta: Feb 18, 2004; Needs More Pirate

gwar3k1 posted:

Microsoft is inclined to agree. Try vbCr instead of vbNewline. Maybe even trim the last vbTab before each newline?

OK apparently my code was alright after all. Now I have an even stupider problem.

It works perfectly on windows 7 with office 2007 (all references the same) but doesnt paste properly in windows vista with office 2007 (references still the same.

I hate VBA so much.

EDIT: Just in case anyone was wondering, ActiveX controls dont work with Office for Mac 2011.

Update: Apparently opening a workbook with macros on office for mac then saving it on the mac makes it hosed for windows office. Havent found out exactly why yet.

Baxta fucked around with this message at 07:16 on Aug 5, 2011

# ¿ Aug 3, 2011 00:13

Baxta: Feb 18, 2004; Needs More Pirate

gwar3k1 posted:

Have you moved from 2003 (or the Mac equivalent) to 2011? Do you know that .xlsx files don't allow macros where as .xlsm do? Sorry if you already know this, but its the first thing that I'd check. Same goes for templates: .xltm allows macro use and isn't the default.

Yeah its not the issue with the macros. Something in office for mac 2011 does something to the library references in the xlsm.

Even if you just open and save on a mac, it screws up the xlsm. I solved the problem by running parallels.

# ¿ Aug 10, 2011 01:44

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