Refresh on Open with ODBC Connection. Stopped working after Module added

[supportsvc]

For some reason the ODBC Connection set to Refresh on open and even when manually trying to Refresh, even though the connection Properties shows correct, stopped working.

I added a module and it seems that's the cause of it.

I had to change it from .xlsx to .xlsm

The connection buttons no longer work AFTER saving and closing it from adding the Module.

See attached. I added the .xlsm file along with the VBS to launch it and send the email.
And also included the Task Scheduler XML file so I can set it to run on a schedule by launching the VBS.

http://files.engineering.com/getfil...e01-872f-b599a84c049e&file=Backlog-3Days.xlsm

http://files.engineering.com/getfil...f4d-91fd-3e0817fb9e4b&file=AutoRunBacklog.vbs

http://files.engineering.com/getfil...8000&file=Export_and_Email_Backlog_Report.xml

 

[SkipVought]

Hi,

Hmmmmm? When I open the workbook, I get bombarded with refresh attempts.

You have Refresh...
[ol 1]
[li]On Open[/li]
[li]Every minute[/li]
[/ol]

The Backlog PivotTable inside the workbook is the query of RawData. This is where the disconnect happened.
RawData does a query to MAS_CAR as does Backlog Connected.

However you have the Backlog PivotTable referencing the data in RawData. This PT need to be refreshed after the RawData has been refreshed.

So I would turn off the Auto Refreshes (although I suspect that Excel figures out the order of execution) and in the sendBacklogEmail procedure
1) refresh the RawData and

Worksheets(“RawData”).ListObjects(1).QueryTable.Refresh False

2) Refresh the PivotCache prior to publishing the PT report.

Worksheets(“Backlog”).PivotTables(1).PivotCache.Refresh

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Strange,
I still do not get the Running Query refresh when opening nor when pressing the Refresh All button.

I know it does on the 1st open. If you try again, it might do the same thing it is doing on my end.
Save then close and try again.

Sorry, I am new at this VBScripting ...
not sure where to put the two lines of code you have posted?

Also, since I have the date stamp and need to have the part that Attaches the output file be a wildcard and the * isn't working?

Sub sendBacklogEmail()
ChDir "\\TIMBERMAS\SageSQL\MAS90\Reports\Custom\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\\ReportResults\Email\Backlog-3DaysReport-" & Format(Date, "yyyy-mm-dd") & ".pdf"

Dim OutlookApp As Object
Dim OutlookMailItem As Object
Dim myAttachments As Object

Set OutlookApp = CreateObject("Outlook.application")
Set OutlookMailItem = OutlookApp.CreateItem(0)
Set myAttachments = OutlookMailItem.Attachments

With OutlookMailItem
.To = "test@email.com"
.Subject = "Backlog 3 Days Report"
.Body = "Good morning, here is the daily Backlog 3 Days Report."
myAttachments.Add "C:\ReportResults\Backlog-3DaysReport*.pdf"
.send
End With

Set OutlookMailItem = Nothing
Set OutlookApp = Nothing

End Sub

Thank you!

 

[SkipVought]

You need to execute the...
1) the RawData refresh and then
2) the Backlog PivotTable refresh BEFORE you Publish the Backlog report.

BTW the .Refresh False means that the code execution will WAIT until the Refresh is complete.

Also, since I have the date stamp and need to have the part that Attaches the output file be a wildcard and the * isn't working?

Assign the date stamp to a variable. Then use that variable in EVERY reference to the pdf name.
Or assign the whole path & pdf name to a variable for multiple usages.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Thanks, I understand the logic but the worksheet simply will no longer Refresh after saving it to .xlsm and after 1st open from the change on file type.

And with the added code I get

Run-time error '9':
Subscript out of range on the 1st part of the added code

Sub sendBacklogEmail()
ChDir "C:\Custom\"
Worksheets(“RawData”).ListObjects(1).QueryTable.Refresh False
Worksheets(“Backlog”).PivotTables(1).PivotCache.Refresh
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Custom\ReportResults\Email\Backlog-3DaysReport-" & Format(Date, "yyyy-mm-dd") & ".pdf"

Dim OutlookApp As Object
Dim OutlookMailItem As Object
Dim myAttachments As Object

Set OutlookApp = CreateObject("Outlook.application")
Set OutlookMailItem = OutlookApp.CreateItem(0)
Set myAttachments = OutlookMailItem.Attachments

With OutlookMailItem
.To = "test@eamil.com"
.Subject = "Backlog 3 Days Report"
.Body = "Good morning, here is the daily Backlog 3 Days Report."
myAttachments.Add "C:\Custom\ReportResults\MEmail\Backlog-3DaysReport-"*" & ".pdf"
.Display
'.send
End With

Set OutlookMailItem = Nothing
Set OutlookApp = Nothing

End Sub

Although I won't need it to email from Excel, just Refresh and save with a date stamp.
Since when the Outlook Marco code to send All Newest Files email works out.

 

[SkipVought]

This [highlight #8AE234]*[/highlight] is still a problem.

myAttachments.Add "C:\Custom\ReportResults\MEmail\Backlog-3DaysReport-"[highlight #8AE234]*[/highlight]" & ".pdf"

Rather...

myAttachments.Add "C:\Custom\ReportResults\MEmail\Backlog-3DaysReport-" & [b]Format(Date, "yyyy-mm-dd")[/b] & ".pdf"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Ok, thank you

however the run-time '9': error is on the 1st code for refresh on

Worksheets(“RawData”).ListObjects(1).QueryTable.Refresh False

NOTE: When you go into the Data and try to Refresh, it does nothing.
When you view the Queries and Connection and go into Properties to Definitions, you can't Edit, you can't even click Ok.
They do not do anything anymore.

 

[SkipVought]

It's your QUOTE characters!

Use this...

Worksheets("RawData").ListObjects(1).QueryTable.Refresh False

You might need to redo your QueryTable in the RawData sheet if its locked up.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Oh geez. I just copied from here and pasted directly.
But that was it ... the ""

I do not know why it keeps locking up.

I have recreated it and it keeps doing the same thing.

I tried it with another file as there are several reports I'll need to do this for and every single time I make it into a .xlsm, it locks up.

What else do you think can be done to unlock it?

 

[SkipVought]

every single time I make it into a .xlsm

???

Why is there an “every single time”? What’s the process where you’re making all these new .xlsm workbooks?

???

1) SaveAs .xlsm. THEN 2) add your QT.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

That's when I tried creating a new to fix what got broken.
I've tried recreating the Backlog file several times now and with the same result.
That being the connection stops working after saving it as .xlsm and re-opening it to refresh it but not refreshing it from the Refresh, Refresh All, from the Queries and Connections, won't go to Edit, won't click Ok

 

[SkipVought]

That being the connection stops working after saving it as .xlsm

I did make a suggestion re: this that you seem to have ignored.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

I haven't ignored anything purposely ...
it seems to be working now.
how strange, did the same thing.
start a new worksheet, add the connection, create the pivot table.
then save as .xlsm
and then add the Module
save, close, reopen, works. then save, close, reopen, doesn't work.

 

[SkipVought]

I’ll wait.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[supportsvc]

Seems to be working now.

Is there a better way to handle multiple worksheets in a workbook?

The code only does ActiveSheet

NOTE: there are a series of about a dozen Excel reports , of which some (worksheets) are in the same workbook.

So one .xlsm has 5 and another has 4, etc ... totaling 12

The goal:
1) each worksheet to refresh
2) output to PDF
3) email all the current date created / date modified reports in one email
the above seems to be working now
then
4) have Windows Scheduler run the Excel Macros, worksheet refresh and save to PDF Modules on a daily schedule
5) have Windows Scheduler run the Outlook Email Macro

 

[SkipVought]

Is there a better way to handle multiple worksheets in a workbook?

‘
   Dim ws As Worksheet, lo As ListObject

   For Each ws In ThisWorkbook.Worksheets
       For Each lo In ws.ListObjects
           lo.QueryTable.Refresh False
       Next
   Next

Generally this will work. But in your case, Since you have one or more external queries and then internal queries based on data from external queries, I would code the explicit sheet names in the sequence they need to execute. I’d do that from a table, that is loop through the table, rather than looping through the worksheets in the workbook. So if my range of sheet names is ShtNames, a list of sheet names in the order I want the queries executed then...

‘
   Dim rShNm As Range, lo As ListObject

   For Each rShNm In [ShtNames]
       For Each lo In Worksheets(rShNm.Value).ListObjects
           lo.QueryTable.Refresh False
       Next
   Next

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]