×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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/getfile.aspx?folder=9...

http://files.engineering.com/getfile.aspx?folder=4...

http://files.engineering.com/getfile.aspx?folder=c...

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

Hi,

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

You have Refresh...
  1. On Open
  2. Every minute
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

CODE

Worksheets(“RawData”).ListObjects(1).QueryTable.Refresh False 
2) Refresh the PivotCache prior to publishing the PT report.

CODE

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


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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?

CODE

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!

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

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.

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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.

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




This * is still a problem.

CODE

myAttachments.Add "C:\Custom\ReportResults\MEmail\Backlog-3DaysReport-"*" & ".pdf" 

Rather...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Ok, thank you

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

CODE

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.

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

It's your QUOTE characters!

Use this...

CODE

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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?

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

I’ll wait.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

Quote:

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

CODE

‘
   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...

CODE

‘
   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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close