Access 2010 VBA not working for Access 2013 users for excel export 2

[08211987]

Hi,
Has anyone experienced Access 2010 VBA code not working for Access 2013 users? I have an export procedure that I have in many databases but now that Access 2013 is being rolled out by our deployment group the code doesn't work. It is basic VBA that exports a query to a xlsm Template and it is like the object gets lost. I could post the code, just wanted to know if anyone has heard of any issues.
Thanks!

 

[SkipVought]

Hi,

An .xlsm is not a template as a .xltm would be. Its a workbook that contains VBA (macro) code.

So does your VBA export the query or the result set?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[08211987]

Thanks Skip,
I have it defined as an .xlsm because I have VBA code in the template that allows me to log who has opened the file and I post it to another database that they opened it, their windows ID and the date and time they opened it. This allows us to see if people are using our reports. We need to act as "big brother" because we get asked for so much stuff and then they don't use it regularly. The reason I call it a template I have the spreadsheet formatted (titles, header colors, filters) and I have the data exported to a different sheet that then copies it to the formatted sheet. The code all works fine in 2010 but not in 2013.

I wonder if there are some new settings that Excel 2013 users need to have set that allow the access vba code to export to the new version? I was going to look at that tomorrow. I personally don't have 2013 and I need to use a user's machine to debug.

One more thing to note, I was logged into the 2013 machine and when I was debugging, I noticed that when it was exporting and it is exported to 2 worksheets that the name of the xl file when I hovered over the xl icon in the taskbar that it had (Group) in the file name. It did not when I was running it from my 2010 laptop.

Appreciate your help!

 

[08211987]

Skip,
Maybe I didn't totally answer your question. All the export logic is in the Access VBA. It invokes excel, it opens the template and it copies the query result to a worksheet tab that I then copy to the formatted worksheet.
Thanks!

 

[SkipVought]

I'd perform the query in a sheet, that contains the formatting you need (a one time effort). It could be executed automatically when the workbook is opened, or on demand. Solves most of your issues.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[08211987]

Skip this is excellent! I'm going to give you a star even though it didn't totally fix my issue. I had not tried this approach before so I did some research and it works great for one of my worksheets but doesn't show the query of my second worksheet when it displays the list of tables/queries. (it contains 2 worksheets). I saw some posts that it can't handle qry/tbl with calculated fields so I resolved that but it still does not display my resulting query or table (tried it both ways).

So going back to my original approach of Access exporting to excel, still works perfectly for 2010 users but not 2013. Gets a subscript out of range. Found posts that say the "object" has changed in 2013, that it invokes 2 objects so it gets confused. Found some sample code but still does not work for us.

http://answers.microsoft.com/en-us/...t/d94f6053-b1b5-48e0-9975-8cb3913f6be6?auth=1

Quote from above:
I've had a chance to play around with this some more. I was able to finally get the result I was looking for. Instead of using the original code:

Dim ExcelObject as Object
Set ExcelObject = GetObject(...filepath...)
ExcelObject.Application.Visible = True
ExcelObject.Windows(1).Visible = True

(He) added a "workbooks" activate line, so that the code now reads:

Dim ExcelObject as Object
Set ExcelObject = GetObject(...filepath...)
ExcelObject.Application.Visible = True
ExcelObject.Application.Workbooks(1).Activate
ExcelObject.Windows(1).Visible = True

(He said) Adding the "ExcelObject.Application.Workbooks(1).Activate" solved the problem so that it works the same as pre-2013 Access.

But this is from me: I get a message "Object variable or With Bock variable not set" on the added "workbooks" statement.
I'll do some more testing tomorrow.
Thanks for your help!

 

[SkipVought]

As you have Set the ExcelObject with the GetObject function, it is a Workbook object, and all you need do is use the Activate method on it.

I don't understand your previously stated issue with the second sheet???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[08211987]

Skip,
Ok but when I use this, I get an error: I'll clarify tomorrow.
ExcelObject.Application.Workbooks(1).Activate
This is the state of my code now and had to comment some due to errors. I can't test tonight because I only have 2010 at home but will test in the morning.

Set appXL3 = CreateObject("Excel.Application") 'added due to 2013
Set obj = GetObject(report_dir & filename)
obj.Application.Visible = True 'added due to 2013
'obj.NewWindow
'obj.Application.Wookbooks(1).Activate 'added due to 2013
'obj.NewWindow
obj.Windows(1).Visible = True
obj.Application.ScreenUpdating = True

What I mean by my 2nd sheet, my first sheet shows the table or query (trying it both ways) but does not for my second sheet. One other thing I might want to add is I am saving this db as a accde so that I can get around some issues I was having with multiple people going against a server shared accdb. Also did some searches and they say that db type should also be accessible for this.

Appreciate your time with this - amazing!

 

[SkipVought]

So did you use Data > Get external data > From Access on your sheets?

That's how to add a QueryTable to your sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

In office 2013+ initially there is no window of opened workbook, adding temporary workbook works:

Dim ExcelObject As Object, ExcelObjectTmp As Object
Set ExcelObject = GetObject(...)
ExcelObject.Application.Visible = True
Set ExcelObjectTmp = ExcelObject.Parent.Workbooks.Add
ExcelObject.windows(1).Visible = True
ExcelObjectTmp.Close
Set ExcelObjectTmp = Nothing

combo

 

[08211987]

Thanks Combo, see below. Skip, yes I did do that and it only displayed the first query. I'll try it again.

I'm getting a Run-Time error 'Application-defined or object-defined error' on this statement:
.Application.Sheets("PLR List").Select '(it's like it never opened it)
I moved the ExcelObjectTmp.Close and ExcelObjectTmp = Nothing to the end in case that was the issue
The ExcelObject = GetObject(report_dir & filename) is not opening at all now, only a blank form see notes below
And when the error happens the blank Excel session is all that is open, I can tell it released the ExcelObject.


Here is the entire code:
Public Sub Print_PLRs()
Dim template_file As String
Dim report_dir As String
Dim filename As String
DoCmd.SetWarnings False
template_file = "\\msfs05.lowes.com\data1\SHARE\MDCM_Reports\Product_Information\PI Item Setup Tracker\Templates\PLR_Item_List_Template.xlsm"
filename = hold_logon_id & "_PLR_Item_Export_Report.xlsm"
report_dir = "C:\PI_Item_Setup_Tracker\"
FileCopy template_file, report_dir & filename 'copies the template locally so no conflict with multiple users
If PLRs_Exported = "N" Then 'in this case it is "N"
DoCmd.TransferSpreadsheet acExport, 10, "qry_tbl_PLR_List", report_dir & filename, False, "PLR_Data" 'exports all PLRs
DoCmd.TransferSpreadsheet acExport, 10, "qry_tbl_Item_Detail", report_dir & filename, False, "Item_Data"
Else
DoCmd.TransferSpreadsheet acExport, 10, "qry_Selected_PLR_List", report_dir & filename, False, "PLR_Data" ' exports selected PLRs
DoCmd.TransferSpreadsheet acExport, 10, "qry_Selected_tbl_Item_Detail", report_dir & filename, False, "Item_Data"
End If

Dim ExcelObject As Object
Dim ExcelObjectTmp As Object 'added objTmp due to 2013
Dim xlApp As Excel.Application
Dim appXL3 As Excel.Application
Dim blnStartXL3 As Boolean

Set appXL3 = CreateObject("Excel.Application")
Set ExcelObject = GetObject(report_dir & filename) 'gets it ok - can tell in explorer the file is open
ExcelObject.Application.Visible = True 'opens a blank excel - no sheets displayed 'added due to 2013
Set ExcelObjectTmp = ExcelObject.Parent.Workbooks.Add 'displays blank workbook with 3 blank sheets, sheet1-3 'added objTmp due to 2013
ExcelObjectTmp.Windows(1).Visible = True 'no change, same as above
'ExcelObjectTmp.Close 'closes blank workbook leave empty excel open 'tried moving to the end 'new 2013
'Set ExcelObjectTmp = Nothing 'at this point only blank excel open but you can tell ExcelObject still open

On Error Resume Next
' Check if Excel is already running
Set appXL3 = GetObject(, "Excel.Application")
If appXL3 Is Nothing Then
blnStartXL3 = True
'Else
'We have to start Excel ourselves
Set appXL3 = CreateObject("Excel.Application")
If appXL3 Is Nothing Then
MsgBox "Can't start Excel", vbExclamation
GoTo exit_handler
End If
End If

With appXL3
On Error GoTo Err_Handler
With appXL3
'Process 1st sheet "PLR List"
.Application.DisplayAlerts = False
.Application.Sheets("PLR List").Select 'this is now erroring and the workbook is not opened, just the blank excel window
.Application.Cells.Select
.Application.Selection.ClearContents

.Application.Sheets("PLR_Data").Select
.Application.Cells.Select
.Application.Selection.Copy

.Application.Sheets("PLR List").Select
.Application.Cells.Select
.Application.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Application.Sheets("PLR_Data").Select
.Application.CutCopyMode = False
.Application.ActiveWindow.SelectedSheets.Delete

'2nd worksheet Item List
.Application.DisplayAlerts = False
.Application.Sheets("Item List").Select
.Application.Cells.Select
.Application.Selection.ClearContents

.Application.Sheets("Item_Data").Select
.Application.Cells.Select
.Application.Selection.Copy

.Application.Sheets("Item List").Select
.Application.Cells.Select
.Application.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.Application.Sheets("Item_Data").Select
.Application.CutCopyMode = False
.Application.ActiveWindow.SelectedSheets.Delete

.ActiveWorkbook.Save

End With
End With

 

[SkipVought]

I don't understand why you're still running code from Access???

His can ALL be done in Excel with a modest amount of VBA.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[08211987]

I've gotten the code the work by adding the line below with the comment. The only thing is that it does still have an open blank session which closes when you close the workbook it modified.
Dim obj As Object
Dim xlApp As Excel.Application
Dim appXL3 As Excel.Application
Dim blnStartXL3 As Boolean

Set obj = GetObject(report_dir & filename)
obj.Application.Visible = True
obj.Application.Workbooks(1).Activate 'added - needed for 2013
obj.Windows(1).Visible = True
obj.Application.ScreenUpdating = True

I found this on this post:

http://answers.microsoft.com/en-us/...t/d94f6053-b1b5-48e0-9975-8cb3913f6be6?auth=1

Skip, I did run another test and it still does not show the query for the 2nd worksheet. For some reason it doesn't show all tables or queries that are in that database. I understand from what I read it won't for calculated fields but this one does not have any. Oh well, thanks for your help! At least the code is working now!

 

[SkipVought]

it still does not show the query for the 2nd worksheet

???

So you manually added a query to the second sheet, but it is not there???

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[08211987]

I would much rather do it in Excel, just can't figure out why I don't see the query in list of views and tables.
Because now I am finding that code is not working in all databases. I'll have to mess with it again tomorrow.
Thanks!

 

[SkipVought]

You may need to recast the Access query in MS Query.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

I think some ome explanations explanations are necessary:

1. What the piece of my code does:
As G[tt]etObject[/tt] opens excel file, but it seems that it can't be made visible (via its window) directly, we need to somehow activate it. In the above:
[ul]
[li]open the file ([tt]ExcelObject[/tt] variable, workbook),[/li]
[li]make excel ([tt]ExcelObject.Parent[/tt]) visible,[/li]
[li]add dummy new workbook ([tt]ExcelObjectTmp[/tt]). After this the first workbook ([tt]ExcelObject[/tt]) seems to be accessible,[/li]
[li]so make it visible ([tt]ExcelObject.Windows(1).Visible=True[/tt]),[/li]
[li]and close and clear unnecessary empty dummy workbook ([tt]ExcelObjectTmp[/tt]).[/li]
[/ul]
In your code you are making visible already visible dummy workbook, the first is still hidden. This may be the reason that you cant select its worksheet.
It's standard that code creates excel instance hidden and it needs to be made visible. What is new is the problem with accessibility of worksheet's window in newer excel versions (or it is windows version problem, anyway, exists).

2. You could brush up your code and make it more solid, some tips:
[ul]
[li]looks like you use early binding, why not, instead of [tt]CreateObject[/tt] or [tt]GetObject[/tt] simply create new excel instance ([tt]Set appXL3=New Excel.Application[/tt]) and open workbook?[/li]
[li]why do you need two excel instances ([tt]xlApp[/tt] and [tt]appXL3[/tt])? (at least they are declared)[/li]
[li]why not declare [tt]ExcelObject[/tt] and [tt]ExcelObjectTmp[/tt] explicitly as [tt]Excel.Workbook[/tt]?[/li]
[li]why do you assign excel to [tt]appXL3[/tt] again (after [tt]On Error Resume Next[/tt], NB the test should be at the beginning, you have already played with excel before),[/li]
[li][tt]appXL3[/tt] is already an excel application, so in the block [tt]With appXL3...End With[/tt] you can skip "[tt].Application[/tt]".[/li]
[/ul]

combo

 

[08211987]

Thanks for the explanations, very helpful. All is working now.
Skip, I'll continue to try your approach, I'm wondering if it has anything to do with the fact the file is a .TDE.
Thanks you both again!