Which Export to Excel Process is best to copy data into an existing formatted spreadsheet?

[JMongi]

Well, I sort of solved it, LOL.
So, something with defining the excel object types at the beginning, even though they aren't being set until later caused the error.
Now, I just don't get any output at all! It makes it through the export command but no file is generated.

Edit: Scratch that....something funky is going on.
So, if I comment out the Excel Object Dims, it doesn't error on the transfer command. But, even if I move the Dims to after the transfer command and leave them uncommented, it errors. I'm going to try and compartmentalize the code into two functions, the export and the format and see if structuring it that way gets me out of this mess.

 

[Isaac]

Well, I sort of solved it, LOL.
So, something with defining the excel object types at the beginning, even though they aren't being set until later caused the error.
Now, I just don't get any output at all! It makes it through the export command but no file is generated.

Edit: Scratch that....something funky is going on.
So, if I comment out the Excel Object Dims, it doesn't error on the transfer command. But, even if I move the Dims to after the transfer command and leave them uncommented, it errors. I'm going to try and compartmentalize the code into two functions, the export and the format and see if structuring it that way gets me out of this mess.

I think we might be back to the age-old "Please post all your code even if it doesn't seem relevant right now".
No idea if I'll be able to spot the problem, but....As you can see, sometimes the problem isn't where you think it is..

 

[JMongi]

I think we might be back to the age-old "Please post all your code even if it doesn't seem relevant right now".
No idea if I'll be able to spot the problem, but....As you can see, sometimes the problem isn't where you think it is..

That is good advice. I have made a few threads where when I posted the full code it led to confusion and answers that didn't address the real problem. I do believe I posted the relevant code, including those definitions in post #30. There are a lot of code snippets posted in this thread.

Here is the full procedure:

Private Sub btnExport_Click()
On Error GoTo ErrHandler

Dim oXL As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim qryExport As String
Dim excelFileName As String
Dim excelFilePath As String
Dim CodeFileName As String
Dim CodeFilePath As String
Dim ShtName As String
Dim CurrentDate As String
Dim excelFile As String
Dim lrow As Long
Dim lcol As Long

'Project Specific Information
    CurrentDate = Format(Now(), "DD-MMM-YYYY")
    qryExport = "qryAvailability"
    excelFileName = "UnitAvailabiltyList"
    excelFilePath = "C:\Users\jmongiardo\Documents\"
    CodeFileName = "AvailabilityVB.bas"
    CodeFilePath = "C:\Users\jmongiardo\Documents\"
    ShtName = "UNIT AVAILABILITY LIST"
    excelFile = excelFilePath & excelFileName & ".xlsm"

Debug.Print qryExport
Debug.Print excelFile
'Transfer data from Access to Excel spreadsheet
    DoCmd.TransferSpreadsheet _
        TransferType:=acExport, _
        SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:=qryExport, _
        filename:=excelFile

'Create a new instance of Excel and make it visible.
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True 'Remove when finished
    Set oBook = oXL.Workbooks.Open(excelFile)
    Set oSheet = oBook.Sheets(1)

'Format the spreadsheet
    oSheet.Name = ShtName
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    lcol = Cells(1, Columns.Count).End(xlToLeft).Column
    With Range(Cells(1, 1), Cells(1, lcol))                         'Format header cells
        .Interior.Color = 49
        .Font.Color = 2
        .Font.Bold = True
        .WrapText = True
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
    With Range(Cells(1, 1), Cells(lrow, lcol))                      'Format column borders
        .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With
    With Range(Cells(2, 1), Cells(lrow, lcol))                      'Format alternate row shading and column width
        .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD (ROW(),2)=0"
        .FormatConditions(1).Interior.Color = 15
        .Columns.AutoFit
    End With
    oSheet.Activate                                                 'Freeze top row so column headers always visible
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
    End With
   
'Import the stored VBA code
    oXL.VBE.ActiveVBProject.VBComponents.Import CodeFilePath & CodeFileName

'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:
'Ensure objects are released
Set oSheet = Nothing
Set oBook = Nothing
Set oXL = Nothing
Exit Sub

ErrHandler:
Call ErrProcessor
Resume ExitHandler
   
End Sub

The only difference is the local location for the export.

 

[Isaac]

And your sure the debug highlighted line of code is on the transferspreadsheet?
This is definitely weird..must be something else going on..

 

[Isaac]

Edit....actually, I get the same error you do.
Never tried to export docmd.transferspreadsheet to an xlsm before.

Just reproduced your problem 100% on my end. Then changed it to xlsx and it worked great.

I really wouldn't recommend going down the road of extensibility unless you have some very special needs. I tried the same thing at one point in the past, importing .bas to give a user a fully loaded tool, and ran into some issues. Also it's possible at some point an antivirus program will have some issue with this. I can't remember the other drawbacks of going down the extensibility road but I ultimately decided it wasn't worth it and to just save the fully loaded template and put stuff into it. Maybe another thing (comes to mind just now) is confidential code, but may be N/A in your case.

I suggest ... just create the xlsm template, save it fully loaded, and docmd.transferspreadsheet somewhere else, then copy the sheet in. (personal opinion)

 

[JMongi]

So .xlsx works for you?
I tried to change that up and am getting a different error now but still on transferspreadsheet.
That's an ironic recommendation because I believe @Minty said exactly the opposite early on. That it was better to directly create the excel file. LOL!

Never tried to export docmd.transferspreadsheet to an xlsm before.

It may be telling that when you manually export a query, you don't get the option to save as .xlsm, only .xlsx

 

[JMongi]

Also it's possible at some point an antivirus program will have some issue with this.

I should have thought of this! We have webroot installed and managed by our 3rd party IT contractors. Woudn't be the first time its caused unexplained issues.

 

[Isaac]

So .xlsx works for you?
I tried to change that up and am getting a different error now but still on transferspreadsheet.
That's an ironic recommendation because I believe @Minty said exactly the opposite early on. That it was better to directly create the excel file. LOL!

Yes - I copied your code, changed only the name of the query to one of mine, changed the Documents path to my username but otherwise left identical, and pretty much left all code identical.
Stepped through it F8, got the EXACT same error on the transferspreadsheet line.

Changed it to xlsx instead, and it worked fine.

Well, to each his own - Minty highly skilled expert, may have reasons I don't know about. I do think creating the spreadsheet using docmd.transferspreadsheet is fine - but you needing to have an end result with VBA project inside it 'changes everything', IMHO. At that point I wouldn't try to create the xlsm. I'd save a master template and push stuff into it. Although if importing the bas works OK for you and seems to have no drawbacks, then go for it - like I said I can't remember some of the reasons I chalked that up as a 'no', it just got too iffy

 

[Isaac]

I should have thought of this! We have webroot installed and managed by our 3rd party IT contractors. Woudn't be the first time its caused unexplained issues.

Makes sense.

A few months ago I was using Filesystemobject Textstream to directly create a text file (except I was actually creating it as .vbs) and was totally speechless while stepping through my code, watching the textstream object just fine in the folder (0 KB in size), but as soon as my line executed:
textstreamobject.close
...then it would disappear. Corporate ninja wufoo for sure!

Eventually I just saved it as .txt, copied it to user's desktop, then changed it to .vbs and it worked.

Sometimes the corporate AV people are sneaky as *** !

 

[JMongi]

I'm definitely going to zag now (although now I have to learn how to stuff it into my template) as the template already exists and is ready to go including the VBA code I need.

Any suggestion on how stuff the data? Copy recordset? Never used it before.

 

[JMongi]

I suppose one thing to check is what reference you have loaded...
Visual Basic for Applications
Microsoft Access 16.0 Object Library
OLE Automation
Micorsoft office 16.0 access database engine object library
Microsoft Excel 16.0 Object Library

Am I missing anything necessary?

 

[Isaac]

I'm definitely going to zag now (although now I have to learn how to stuff it into my template) as the template already exists and is ready to go including the VBA code I need.

Any suggestion on how stuff the data? Copy recordset? Never used it before.

You can use DoCmd.TransferSpreadsheet to a new xlsx, then use Excel Automation (in Access, similar to what you were), to copy that sheet from the new book into the template book.

aircode:

dim excelapp as object, wbSource as object, wbTemplate as object
dim wsSource as object, wsDestination as object
docmd.transferspreadsheet.............etc, to NewBook
set excelapp=createobject("excel.application")

set wbSource = excelapp.workbooks.open(NewBook)
set wbTemplate = excelapp.workbooks.open("path to template workbook")

set wsSource = wbSource.sheets(1) 'probably the first one, all else being equal
wsSource.copy after:=wbTemplate.worksheets(wbTemplate.worksheets.count) 'this physically copies the sheet from the source workbook into the destination workbook, at the END position
set wsDestination = wbTemplate.worksheets(wbTemplate.worksheets.count) 'because we know it is now the last sheet in the book
wsDestination.name="new name if you want"

I do like CopyFromRecordset quite a bit - for when DoCmd.TransferSpreadsheet is cutting off your stuff at 255. I never quite feel like I can 100% trust anything to never truncate, it seems (!), but I have had better luck with CopyFromRecordset:

Get all your excel automation objects fired up - excelapp, a workbook (as object), a worksheet (as object), rng (as object)
set excelapp to the createobject method
set wb object to excelapp.workbooks.open
set wsobject to wb.worksheets(1) 'etc. or "name"
set rng = wsobject.range("A2")
rng.copyfromrecordset rs 'where rs is an open recordset you've opened in access dao

then I also loop through the DAO.FIELDS of the recordset and copy them to row 1 in excel, lemme know if you get that one sorted out

I'd skip all References to excel - it's much better to use late binding when deploying, due to users and their versions/machines
But if your user base is 100% all the same version as you and you can rely on that, then early binding is cool - easier to code w/intellisense
Some people do early binding early (while dev) for intellisense, then convert to late before deploying.

 

[JMongi]

I'd skip all References to excel - it's much better to use late binding when deploying, due to users and their versions/machines
But if your user base is 100% all the same version as you and you can rely on that, then early binding is cool - easier to code w/intellisense
Some people do early binding early (while dev) for intellisense, then convert to late before deploying.

I know what your talking about (the timing of type checking) but don't really have any idea how to differentiate the two in my code.

Also, since I would like to automate this process from a single button click within Acesss, how/where am I running the Excel Automation?

 

[JMongi]

Well, now I'm getting an Error 3011 - Sounds like possible DB corruption. I'm going to try opening a previous version and see if I get the same error with the same code.

 

[JMongi]

Well, I'm completely befuddled. I copied the transfer code to another non split database that I haven't touched in months and I get the same error:

Run-time error '3011':

The Microsoft Access database engine could not find the object 'qryAvailabilty'. Make sure the object exists and that you spell its name and the path name correctly.  If 'qryAvailability' is not local object, check your network connection or contact the server administrator.

 

[JMongi]

 

[JMongi]

Umm...wow...so, even though I checked it a dozen times yesterday; and a few more times this morning....
When my old database didn't work either. I decided to check it again one more time...just to be sure...and....yep
...
...
The actual query itself was misspelled.
And the 2nd query I picked as an alternative test....was also misspelled.
And I spelled them both correctly in my code. So that's why it failed.

 

[Isaac]

I know what your talking about (the timing of type checking) but don't really have any idea how to differentiate the two in my code.

Also, since I would like to automate this process from a single button click within Acesss, how/where am I running the Excel Automation?

Once you create an excel application and a workbook (and probably sheet and ranges) variables, you can do anything they can do.

IF, in excel, you would say range.value="5"
Then, in access, once you have a range variable declared and set, you'd still say range.value="5"

early binding = "as excel.application, excel.workbook, excel.range" etc with a library referenced checked.
late binding = "as object" > set app as createobject() > set the rest the same way you normally would, just pay attention to hierarchy

 

[JMongi]

Thanks for the explanation on early binding vs. late binding. As far as "hierarchy" is concerned, what exactly do you mean? Be careful about not fully spelling out the object (like workbook.worksheet as opposed to .worksheet? or something else?

 

[Minty]

You have to fully qualify the objects in the code at all times, unless you have pre-qualified it in a With block.