cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
smpa01
Super User
Super User

Automate printing after data refresh

Hi,
Is it possible to automate printing the outputs as soon as the query gets refreshed. I process an input using power query and generate the output in excel. I want my outputs to get printed automatically everytime they get refreshed.

Is it possible to achieve?

Thank you in advance.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


1 ACCEPTED SOLUTION
smpa01
Super User
Super User

I managed to reach a solution by using Macro. Not sure if this can be achieved by any other way.

 

The task was to import an input table into PQWRY and generating two outputs and print them upon refresh.

 

Sheet 1 contains Raw data and Sheet 2 and 3 contains output generated by PQWRY- https://drive.google.com/open?id=0B7imabOIHE8QTTFBLWw4aFU5dzg

 

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveWorkbook.Connections("Query - Table1").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM [Table1]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties="""""
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query - Table1")
        .Name = "Query - Table1"
        .Description = "Connection to the 'Table1' query in the workbook."
    End With
    With ActiveWorkbook.Connections("Query - Table1 (2)").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM [Table1 (2)]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1 (2);Extended Properties="""""
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query - Table1 (2)")
        .Name = "Query - Table1 (2)"
        .Description = "Connection to the 'Table1 (2)' query in the workbook."
    End With
    ActiveWorkbook.RefreshAll
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet3").Select
    Range("Table1__2[[#Headers],[Name]]").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

If there any more ways to achieve I would be very intersted to know.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

1 REPLY 1
smpa01
Super User
Super User

I managed to reach a solution by using Macro. Not sure if this can be achieved by any other way.

 

The task was to import an input table into PQWRY and generating two outputs and print them upon refresh.

 

Sheet 1 contains Raw data and Sheet 2 and 3 contains output generated by PQWRY- https://drive.google.com/open?id=0B7imabOIHE8QTTFBLWw4aFU5dzg

 

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveWorkbook.Connections("Query - Table1").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM [Table1]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties="""""
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query - Table1")
        .Name = "Query - Table1"
        .Description = "Connection to the 'Table1' query in the workbook."
    End With
    With ActiveWorkbook.Connections("Query - Table1 (2)").OLEDBConnection
        .BackgroundQuery = False
        .CommandText = Array("SELECT * FROM [Table1 (2)]")
        .CommandType = xlCmdSql
        .Connection = _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1 (2);Extended Properties="""""
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query - Table1 (2)")
        .Name = "Query - Table1 (2)"
        .Description = "Connection to the 'Table1 (2)' query in the workbook."
    End With
    ActiveWorkbook.RefreshAll
    Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Sheet3").Select
    Range("Table1__2[[#Headers],[Name]]").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

If there any more ways to achieve I would be very intersted to know.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors