#Else토론 중인 항목 0개

Hi @Sachin Bankar​ ,

All links are working fine, will you please let me know, which file you are not able to download. You can cross-check it through different browser.

Regards,

Lalit Panwar

Hi Stephen

The key steps are:

1. Use an Excel file as a data source when creating your Tableau file
2. When finished, export your Tableau file as .twbx
3. Manually rename the .twbx it to a .zip.
4. 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

#Else

    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