Which Export to Excel Process is best to copy data into an existing formatted spreadsheet? (1 Viewer)

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
You have to fully qualify the objects in the code at all times, unless you have pre-qualified it in a With block.
That's what I was referring to, didn't know if With blocks didn't work the same or not.
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
late binding = "as object" > set app as createobject() > set the rest the same way you normally would, just pay attention to hierarchy
What I meant was to declare and set all variables that represent all objects you need to work with - an excel application, a workbook, a worksheet, and probably range stuff. Remember each one belongs to its mother and will be hard to associate with if its mother doesn't exist yet.

Honestly, that's the same principles I'd strongly recommend anyone follow if they were coding Excel VBA inside an Excel workbook, too.

In late binding for example, if ALL you wanted to do was open a workbook and make the value of A1 something, you should minimally do:
(i'm skipping the declarations, they're all objects):
set excelapp=createobject("excel.application")
set wb = excelapp.workbooks.open()
set ws = wb.worksheets("Sheet1")
now you can either do
ws.range("A1").value="5"
or
set rng = ws.range("A1")
rng.value="5"

So you're always following a kind of hierarchy and belonging structure.

Whereas if you were doing this INSIDE excel, where you are maybe more used to doing excel vba, you could get away with coding something as (awful, but it may work most of the time), like:

Range("A1").value="5"

...which many people do because it seems to work for a while. Until, of course, you deploy it to 20 users, some of whom have 8 other excel sheets flying around their screen at the exact same time.
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
You have to fully qualify the objects in the code at all times, unless you have pre-qualified it in a With block.
Or unless you have Set very granular variables already, which is what I usually do.

So this:
Excelapp.workbooks("book1.xlsx").sheet("sheet1").range("a1").value="5"

...is obviously something we all avoid, in diff ways.

Some people avoid it by using With blocks. I don't use With almost ever, and instead just set granular variables. I find myself referring to them a lot more later on anyway - but both ways work, just personal preference.
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
I'm less familiar with With blocks, but started using them for certain commands where it was much easier to see all the switches using a with block than a big line of text.
I usually fall under the granular variable side of things and am also the user with 8 sheets open, so specificity is important!
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
Coming along...(I think). Here's a question for you smartypants ;)

If there is code in the Workbook Open event and that file is "opened" using VBA in Access, does that embedded code also get run?
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
I believe it will depend on whether that user's specific Excel application's Trust Center Settings are set to with regard to the Macros category.
However, I have come to completely distrust those settings - they aren't reliable.

In order to be more sure, you can remove the code from workbook open, move it to a regular (public) sub, and run it application.run
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
I don't need it, just a curiosity that popped in my head.
I do have a legit question since I'm very light in using objects. With late binding, what do I need to release? And If I need to release it all, is there a fast way to do so (since I'm currently at 9 objects).
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
Almost there.......
So, I've got my button code in Access. Almost everything is working. Here is the button code:
Code:
Private Sub btnExport_Click()
On Error GoTo ErrHandler
'Variable Definitions
    Dim qryExport As String
    Dim excelFileName As String
    Dim excelFilePath As String
    Dim excelFile As String
    Dim templateFileName As String
    Dim templateFilePath As String
    Dim templateFile As String
    Dim destinationPath As String
    Dim ShtName As String

'Query Export Information
    qryExport = "qryAvailabilityList"
    excelFileName = "UnitAvailabilityList"
    excelFilePath = "\\**REDACTED**"
    excelFile = excelFilePath & excelFileName & ".xlsx"
    Call ExportQuery(qryExport, excelFile)
   
'Format List Information
    templateFileName = "TestTemplate.xlsm"
    templateFilePath = excelFilePath
    templateFile = templateFilePath & templateFileName
    ShtName = "UNIT AVAILABILITY LIST"
    Call FormatList(excelFile, templateFile, ShtName)

ExitHandler:
'Any needed code
Exit Sub

ErrHandler:
Call ErrProcessor
Resume ExitHandler
   
End Sub

The qryExport subroutine works with no errors. The FormatList subroutine makes it almost all the way. Here it is:

Code:
Public Sub FormatList(Source As String, Template As String, SheetName As String)
On Error GoTo ErrHandler

'Variable Definitions
    Dim excelApp As Object
    Dim wbSource As Object, wbTemplateC As Object
    Dim wsDestination As Object, wsSource As Object, wsTemplateC As Object
    Dim rngDestination As Object, rngTemplateC As Object
    Dim fso As Object
    Dim TemplateC As String
    Dim lastrow As Long, lastcol As Long

'Create Copy of List Template File
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")                        'Create File Object
    TemplateC = Left(Template, Len(Template) - 5) & "(1).xlsm"
    fso.CopyFile Template, TemplateC                                                'Make Template copy

'Transfer data from export source to copy of template file
    Set excelApp = CreateObject("Excel.Application")                                'Create excel instance
    Set wbSource = excelApp.Workbooks.Open(Source)                                  'Open Source workbook
    Set wbTemplateC = excelApp.Workbooks.Open(TemplateC)                            'Open Template copy
    Set wsTemplateC = wbTemplateC.Worksheets(1)                                     'Set Template copy sheet
    Set wsSource = wbSource.Sheets(1)                                               'Set source workbook sheet
    wsSource.Copy after:=wbTemplateC.Worksheets(wbTemplateC.Worksheets.Count)       'Copy source worksheet to end of template copy
    Set wsSource = Nothing
    Set wbSource = Nothing
    Set wsDestination = wbTemplateC.Worksheets(wbTemplateC.Worksheets.Count)        'Set New List Destination Sheet
    lastrow = wsDestination.UsedRange.Rows.Count
    lastcol = wsDestination.UsedRange.Columns.Count
    Set rngDestination = wsDestination.Range(Cells(2, 1), Cells(lastrow, lastcol))  'Set destination data range
    Set rngTemplateC = wsTemplateC.Range(Cells(2, 1), Cells(lastrow, lastcol))      'Set templateC data range
    rngTemplateC.Cells.Value = rngDestination.Cells.Value                           'Set templateC data equal to destination data

'Format alternating row shading and column widths
    With Range(rngTemplateC)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD (ROW(),2)"
        .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 15
        .Columns.AutoFit
    End With

'Turn instance of Excel over to end user and release any outstanding object references.
    'oXL.UserControl = True
    'Set oSheet = Nothing
   ' Set oBook = Nothing
    'Set oXL = Nothing
   
ExitHandler:
'needed code
Exit Sub

ErrHandler:
Call ErrProcessor
Resume ExitHandler
End Sub

It currently errors on "Set rngDestination =" line

Error 1004: Application-defined or object-defined error
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
Remember those hierarchies your warned me about? :)
Solution, Cells(R1,C1) is an implicit call even though its within the ws.range wrapper.
So the line should be:
Code:
Set rngDestination = wsDestination.Range(wsDestination.Cells(2, 1), wsDestination.Cells(lastrow, lastcol))  'Set destination data range
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
@Isaac, @Minty
The last hurrah....conditional formatting.

Here's the latest and greatest sub. FormatConditions.Delete passes fine. It fails on FormatConditions.Add
Code:
Public Sub FormatList(Source As String, Template As String, SheetName As String)
On Error GoTo ErrHandler

'Variable Definitions
    Dim excelApp As Object
    Dim wbSource As Object, wbTemplateC As Object
    Dim wsDestination As Object, wsSource As Object, wsTemplateC As Object
    Dim rngDestination As Object, rngTemplateC As Object
    Dim fso As Object
    Dim TemplateC As String
    Dim lastrow As Long, lastcol As Long

'Create Copy of List Template File
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")                        'Create File Object
    TemplateC = Left(Template, Len(Template) - 5) & "(1).xlsm"
    fso.CopyFile Template, TemplateC                                                'Make Template copy
    Set fso = Nothing

'Transfer data from export source to copy of template file
    Set excelApp = CreateObject("Excel.Application")                                                           'Create excel instance
    Set wbSource = excelApp.Workbooks.Open(Source)                                                       'Open Source workbook
    Set wbTemplateC = excelApp.Workbooks.Open(TemplateC)                                           'Open Template copy
    Set wsTemplateC = wbTemplateC.Worksheets(1)                                                             'Set Template copy sheet
    Set wsSource = wbSource.Sheets(1)                                                                                 'Set source workbook sheet
    wsSource.Copy after:=wbTemplateC.Worksheets(wbTemplateC.Worksheets.Count)       'Copy source worksheet to end of template copy
    Set wsSource = Nothing
    Set wbSource = Nothing
    Set wsDestination = wbTemplateC.Worksheets(wbTemplateC.Worksheets.Count)        'Set New List Destination Sheet
    lastrow = wsDestination.UsedRange.Rows.Count
    lastcol = wsDestination.UsedRange.Columns.Count
    Set rngDestination = wsDestination.Range(wsDestination.Cells(2, 1), wsDestination.Cells(lastrow, lastcol))          'Set destination data range
    Set rngTemplateC = wsTemplateC.Range(wsTemplateC.Cells(2, 1), wsTemplateC.Cells(lastrow, lastcol))                 'Set templateC data range
    rngTemplateC.Cells.Value = rngDestination.Cells.Value                                                                                            'Set templateC data equal to destination data
    Set rngDestination = Nothing
    Set wsDestination = Nothing

'Format alternating row shading and column widths
    With rngTemplateC
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD (ROW(),2)"
        .FormatConditions(1).Interior.ColorIndex = 15
    End With
    Set rngTemplateC = Nothing
    Set wsTemplateC = Nothing
    Set wbTemplateC = Nothing
    Set excelApp = Nothing
   
ExitHandler:
'needed code
Exit Sub

ErrHandler:
Call ErrProcessor
Resume ExitHandler
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
I don't need it, just a curiosity that popped in my head.
I do have a legit question since I'm very light in using objects. With late binding, what do I need to release? And If I need to release it all, is there a fast way to do so (since I'm currently at 9 objects).
Lots of separate posts, I'll answer them in order...as I'm not sure what you still do or don't need....

You virtually never need to set objects back to nothing in VBA. They terminate when they go out of scope. The one exception might be dao recordsets if numerous are opening in a loop, but even there, I'm pretty sure it's more about recordset.close versus set recordsetvariable=nothing.
If you're creating Classes and stuff like that, there might be different rules, but you're not doing that and I'm unsure as I have never truly used them.

But, not having to Set to Nothing notwithstanding, you still need to take practical steps like:
- Closing (application.quit) any program instance (Excel, etc) that you open.
- Closing open workbooks, etc

It currently errors on "Set rngDestination =" line

Error 1004: Application-defined or object-defined error
I was going to say check the values of lastcol and lastrow, to make sure that it makes sense to define the range as upper-left A2:##, but it looks like you got it working now? YES - that's a great example where (I personally) find it easier to just always define granular variables - because now you need something nice and short, like fixing Cells() to instead of ws.Cells()

For the format conditions - That's an example of a function that is too 'out there' to have memorized - so I'd be only relying on whatever insight the macro gives me (or if all else fails check the documentation ha) - what is the error?
Wait - you're using late binding and do NOT have any references to Excel checked in Tools>References? If "correct", then the problem is xlExpression
When late binding, ALL special little excel constants have to be converted to their numerical value.
When in doubt, I just google: "xlExpression enumeration" and usually the top result is the MS page with the corresponding Number value.

Somebody once provided a text file on the web you could paste in a module that would declare ALL excel constants, so that you could continue making references that looked like that even while late binding, but I can never find it any more
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
Wait - you're using late binding and do NOT have any references to Excel checked in Tools>References? If "correct", then the problem is xlExpression
Good info on the set = nothing but this line is the savior. Should have known about xl anything since I had to redo my lastrow, lastcol code for latebinding.
I'll check it out Monday but I'd say that's the winner. Thanks!
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
And hopefully you have option explicit at the top of the module, AND, have it 'required' in tools>options so it automatically goes in all modules any time you write vba in that office program on your machine......So that this problem is revealed at compile-time, nice and early.
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
@Isaac - I have Option Explicit in there. I don't see exactly what your specifying in Tools > Options unless its called something different.
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
So, xlExpression enumeration is 2 per this page. But the following code snippet (see post #70 for the full block):
Code:
With rngTemplateC
        .FormatCondetions.Delete
        .FormatConditions.Add Type:=2, Formula1:="=MOD(ROW(),2)"
        .FormatConditions(1).SetFirstPriority
        .FormatConditions(1).Interior.ColorIndex = 15
        .FormatConditions(1).StopIfTrue = False
End With

This code failed on line 3 during run-time with error 438 - object doesn't support this property or method

This line from the help file might be relevant:
  • You specified a Friend procedure to be called late bound. The name of a Friend procedure must be known at compile time. It can't appear in a late-bound call.
Thoughts?
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
Ok, so this appears to be sorted!
There were two errors going on. One was due to the typo you can see in the format conditions.delete. That wasn't in the original code from last week, that was me typoing this morning during my rewrite.
So, I reworked the code slightly and discovered the error is with the formatting code itself. Here is the newly formatted code block:

Code:
 With rngTemplateC
        .FormatConditions.Delete
        .FormatConditions.Add Type:=2, Formula1:="=MOD(ROW(),2)"
        With .FormatConditions(1).Interior
            .ColorIndex = 15
            .TintAndShade = 0
            .StopIfTrue = False
        End With
End With

This errored on .stopIFtrue = false (default is true). It actually doesn't matter for my application. I removed it and voila! No errors.
Code:
 With rngTemplateC
        .FormatConditions.Delete
        .FormatConditions.Add Type:=2, Formula1:="=MOD(ROW(),2)"
        With .FormatConditions(1).Interior
            .ColorIndex = 15
            .TintAndShade = 0
        End With
End With
 

Minty

AWF VIP
Local time
Today, 12:34
Joined
Jul 26, 2013
Messages
10,354
You need to bring that back one level to make it work - it should be
Code:
.FormatConditions(1).StopIfTrue = False
It's not part of the Interior properties.
 

JMongi

Active member
Local time
Today, 08:34
Joined
Jan 6, 2021
Messages
802
@Minty - Thanks! I had avoided the With clause for .interior until this morning. So that was a new error I introduced.

It looks like the main issue was the "xlExpression" as @Isaac pointed out earlier and any other errors I introduced trying to rewrite the conditional formatting to fix the main issue. This was very educational and I learned a lot about the interactions between Excel and Access and early binding vs. late binding. Thanks to @Isaac, @Minty and @arnelgp for their assistance!

I have an additional question that @Isaac touched on already, but I want to make sure I understand it related to my current code block. What things do I need to close at the end of my code block? See post #70 for the full block.
 

Minty

AWF VIP
Local time
Today, 12:34
Joined
Jul 26, 2013
Messages
10,354
As @Isaac said - and I agree, it's important to close anything that you open.
Excel is particularly good at leaving a process running if you error out of it, so once you get your code fixed and tested add the following to any error handler.

excelapp.Workbooks.Close
Set excelapp = Nothing

This will tidy up in the case of any error. You also don't appear to be saving the file after the export.
So Close any object you open.
 

Isaac

Lifelong Learner
Local time
Today, 05:34
Joined
Mar 14, 2017
Messages
8,738
excelapp.Workbooks.Close
Set excelapp = Nothing

But remember to quit the excelapp, too.

I've noticed that once you use the app to open a workbook, the app stays open unless you quit it.

On my computer anyway, the following code leaves excel open

Code:
Sub Test()
Dim excelapp As Object, wb As Object
Set excelapp = CreateObject("excel.application")
excelapp.Visible = True 'i just did this for ease of testing & knowing what's happening
Set wb = excelapp.workbooks.Open("C:\Users\isaac\Desktop\Bills\Bills 2021-07-16.xlsb")
wb.Close False
Set excelapp = Nothing
End Sub
 

Users who are viewing this thread

Top Bottom