Excel file opens, and code stops execution (1 Viewer)

[marlan]

Hi All you experts!

I'me running on Win7, Office 2010, Acc2003 format (.mdb), writing data to Excel 2010 (.xlsx)

I have code to create an Excel file, send record set data to it, and then add some formulas and formatting.
I was trying to tune up the formulas, when I got a pull back:
Access VBA code suddenly stops running, and the file gets displayed in Excel:banghead:

'ApXl is Application.Excel
'xlWSh is the Excel WorkSheet
'rst is my DAO.Recorset

ApXl.Visible = False
...
'After this line code stops, and the file gets displayed in Excel:
xlWSh.Range(Col & CStr(row)).CopyFromRecordset rst

ApXl is Application.Excel
xlWSh is the Excel WorkSheet
rst is my DAO.Recorset

The same happens when I have ApXl.Visible = True

Any Ideas?

Thanks in advance!

 

[pr2-eugin]

You are only showing the skeleton, I thing the Set rst would be a problem. Or the Range is out of scope ! Something is not right. Have you tried stepping through the code?

 

[marlan]

Hi Paul, and thank-you for replying!

yes, I tried with Exl app visible.

this code and recordset has bin running fine for a few days.

 

[pr2-eugin]

Exl app visible does nothing. If you set it to false you will not be seeing the Excel book populated. That is all. Your code in Access is the problem not with Excel.

 

[marlan]

Some more info:
the process creates some scrap file, with a random name (as: '32DCA500', no extention).
I cannot delete these files, even after stopping Access and Excel

 

[marlan]

Hi again,

Rebooting the computer has removed the above temp files, but I still get the same problem. no error or message, code just stops, And Excel becomes visible, rst data Populated.

I'll add: I had a bug before, attempting to set value to Cell in Col "_" (five Cols after Chr(Acs("Z") +5), should have bin Col "AE"...). The code now doesn't reach the point I can test it...

This is my full code:

Public Function SendRstData2XLSheet(rst As DAO.Recordset, ByRef ApXl As Object, ByRef xlWBk As Object, _
                                ByRef xlWSh As Object, _
                                Optional Col As String = "A", Optional row As Integer = 1, _
                                Optional isVisible As Boolean = False)
    
    Dim ErrNum As Long    
    Dim TempColWidth As Integer

    SendRstData2XLSheet = 0    
    ApXl.Visible = isVisible

'Add Data:
    rst.MoveFirst
' code stops here:
    xlWSh.Range(Col & CStr(row)).CopyFromRecordset rst
' code execution never reaches this line:
    SendRstData2XLSheet = rst.RecordCount
                
    xlWSh.Range(Col & CStr(row)).Offset(0, 0).Resize(SendRstData2XLSheet, rst.Fields.Count).Select
    With ApXl.Application.Selection.Font
'        Some font stileing
    End With
    
    xlWSh.Range(Col & CStr(row)).Select
    
    ApXl.Application.activeWorkbook.Save
     
Exit_SendRstData2XLSheet:
    If ErrNum > 0 Then _
        Err.Raise ErrNum
    
    Exit Function

Err_SendRstData2XLSheet:
    ErrNum = Err.Number
    Resume Exit_SendRstData2XLSheet
    
End Function

 

[pr2-eugin]

Well that could be your problem ! As explained before, your Range might throw an error. As you see Asc("Z") + 5 is 95. So it does not translate to AE, instead it does as - which is Chr(95).

You might need some code to help you create the column name !

 

[marlan]

Hi,

that was my problem...

in the next sub I format 6 Cols starting "K", 5 Cols apart: K, P, U, Z, _ ->Previous bug.
I wrote code to fix that, came to test it and reached this problem, executed BEFORE the bug I tried to fixed. I think it's no the code, I haven't changed it!

Iv'e restarted Excel, Access, and the entire computer, and get the same problem...

any ideas?

 

[pr2-eugin]

Well this is not entirely the best method, but should get you started. If you pass a number to the function it will give you a column name for excel.

Public Function getStringSeq(tmpVar As Long) As String
    Select Case tmpVar
        Case Is <= 25
            getStringSeq = Chr(tmpVar + 65)
        Case 26 To 51
            getStringSeq = Chr(65) & Chr(tmpVar + 65 - 26)
        Case 52 To 77
            getStringSeq = Chr(66) & Chr(tmpVar + 65 - 52)
        Case 78 To 103
            getStringSeq = Chr(67) & Chr(tmpVar + 65 - 78)
        Case 104 To 129
            getStringSeq = Chr(68) & Chr(tmpVar + 65 - 104)
        Case 130 To 155
            getStringSeq = Chr(69) & Chr(tmpVar + 65 - 130)
        Case 156 To 181
            getStringSeq = Chr(70) & Chr(tmpVar + 65 - 156)
        Case 182 To 207
            getStringSeq = Chr(71) & Chr(tmpVar + 65 - 182)
        Case 208 To 233
            getStringSeq = Chr(72) & Chr(tmpVar + 65 - 208)
        Case 234 To 259
            getStringSeq = Chr(73) & Chr(tmpVar + 65 - 234)
        Case 260 To 285
            getStringSeq = Chr(74) & Chr(tmpVar + 65 - 260)
        Case 286 To 311
            getStringSeq = Chr(75) & Chr(tmpVar + 65 - 286)
        Case 312 To 337
            getStringSeq = Chr(76) & Chr(tmpVar + 65 - 312)
        Case 338 To 363
            getStringSeq = Chr(77) & Chr(tmpVar + 65 - 338)
        Case 364 To 389
            getStringSeq = Chr(78) & Chr(tmpVar + 65 - 364)
        Case 390 To 415
            getStringSeq = Chr(79) & Chr(tmpVar + 65 - 390)
    End Select
End Function

Please not the function is 0 based ! For example,

? getStringSeq(30)
AE
? getStringSeq(6)
G
? getStringSeq(0)
A

 

[marlan]

My post is about a problem that accrues before the problem you are trying to help me with...

 

[marlan]

BTW, this is my (untested) code:

Public Function CalculateColOffseToStrig(Number As Integer) As String
'Number is ASCII val of 'A' + offset.
    Dim temp As Integer, i  As Integer, Devider As Integer, remain As Integer, A As Integer
    Dim Rez As String
    
    temp = Number - Asc("A")
    Devider = Asc("Z") - Asc("A")
    Do While temp > 0
        Rez = Chr(Asc("A") + temp Mod Devider) & Rez
        temp = Int(temp / Devider)
    Loop
    CalculateColOffseToStrig=Rez
End Function