We plan on publishing twbx files for offline access to some of our interactive reports. Each one will have a subset of data in it, for example data for only a single province. Is there a way to use TabCmd to automate this?
Approach #1:
Create a datasource and an extract with a filter for each province
Copy the workbook and create a separate one associated with each datasource
Refresh datasources as needed and then get the workbooks.
I do believe it's automatable with tabcmd once the provincial workbooks with the associated datasources are defined.
This approach is a bummer because if I need to update the master workbook, I then have to create province copies associated with the correct data source. This also applies to the data sources (all use a custom sql query). So if that query changes, I'll have to do it to every data source (or recopy, etc.)
Approach #2:
Create a single workbook.
Manually recreate the filtered extract for each province.
Save the extracted workbook.
This seems the simplest, but I don't see any tabcmd's to either replace a datasource in a workbook, or set a filter for an extract.
Does anyone have any ideas here?
Thanks in advance.
Hi Stephen
The key steps are:
- Use an Excel file as a data source when creating your Tableau file
- When finished, export your Tableau file as .twbx
- Manually rename the .twbx it to a .zip.
- Open the zip and delete the cache (if one exists)
That zip then becomes the template to create other twbx files, each with a different data set.
The zip contains an Excel file, and it’s this file you swap in and out with VBA or Powershell to generate new twbx files.
The location of the Excel file in the zip is
Excel set up:
Have a single Excel Template Workbook with a table containing a data connection to SQL. (if you need multiple data sources, add each on as extra sheets as below)
Have a single Excel Control Workbook which contains a VBA loop to:
- Open template
- Update SQL connection details for next customer each customer Example: Change [Exec MySQLProcedure @Customer = X] --> [Exec MySQLProcedure @Customer = Y]
- Refresh the SQL connection to bring back new data
- Break the SQL connection
- Save the into it’s own directory: \MyCustomerName\Data\Documents\MyExcel.xlsx
- Repeat for all merchants
The name of the Excel file should be identical in every case. However it should be saved into a new MyCustomerName directory.
(I can send you some code for this Excel process if you like, however I'll leave it off for now to keep the post size down)
VBA for replacing files inside a zip:
After the template has created multiple .xslx files, you swap them into the .zip file.
The code I used for this is below:
'---------------------Sets up Winzip shell extension for VBA-----------------------
'---------------------Place this at the top of your code module--------------------
#If VBA7 Then
Private Declare PtrSafe Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long
#End If
Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103
'----------------------------------------------------------------------------------
Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long, hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win64. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub
'----------------------------Code for creating zip files--------------------------------------
'1.Copies Template zip and renames as merchant 2.Adds merchant data folder to zip
Sub CreateTableauWorkbooks
Dim sZipProgramPath As String
Dim sZipTemplatePath As String, sFolderToBeZipped As String, sZipNewFilePath As String
Dim sShellStr As String
Dim rZipName As Range
sZipProgramPath = “C:\program files\winzip\”
sZipFolderPath = ThisWorkbook.Path & "\Tableau\" ‘This is where they get saved
For Each rZipName In Range("MerchantTable[Customer_Name]") ‘loop a list of merchants
sFolderToBeZipped = sZipFolderPath & rZipName.Value & "\" 'Zips all files inside this folder
sZipNewFilePath = sZipFolderPath & rZipName.Value & ".zip" 'New name of zip created
sZipTemplatePath = sZipFolderPath & Range("TemplateZipName").Value
FileCopy sZipTemplatePath, sZipNewFilePath 'Create Zip File ready for new data
If Dir(sZipProgramPath & "winzip64.exe") = "" Then Exit Sub 'Check if this is the path where WinZip is installed.
On Error Resume Next
sShellStr = sZipProgramPath & "Winzip64.exe -min -a -r" & " " & Chr(34) & sZipNewFilePath & Chr(34) & " " & Chr(34) & sFolderToBeZipped & "*.*" & Chr(34)
Call ShellAndWait(sShellStr, vbHide)
Name sZipNewFilePath As sZipFolderPath & rZipName.Value & ".twbx"
Next
End Sub
Robin
