{"id":2332,"date":"2024-09-29T18:05:34","date_gmt":"2024-09-29T18:05:34","guid":{"rendered":"https:\/\/haimagazine.com\/?p=2332"},"modified":"2025-06-26T15:03:44","modified_gmt":"2025-06-26T13:03:44","slug":"automation-with-ai-part-1-creating-vba-scripts-with-chatgpt","status":"publish","type":"post","link":"https:\/\/haimagazine.com\/en\/ai-how-to\/automation-with-ai-part-1-creating-vba-scripts-with-chatgpt\/","title":{"rendered":"Automation with AI (Part 1) | Creating VBA Scripts with ChatGPT"},"content":{"rendered":"

<\/p>

Will ChatGPT replace accountants? No, but it can make their lives easier, especially in areas that previously required really good relations with the IT department.<\/p>

Technology is advancing at an incredible pace, and with it, the amount of data in our organizations is growing. What else has significantly increased in recent years? The number of new obligations resulting from regulatory changes. The effect? Piles of additional, often tedious and repetitive work, and massive amount of data to process. I will show you examples of how we can perform these routine and tedious processes more efficiently by automating them appropriately. This way, we can dedicate more time to more interesting tasks that bring value and satisfaction. This is here generative artificial intelligence comes to our aid, so that using only natural language (and therefore no programming) we can, among other things, create effective macros or find inspiration for improvements in other processes.<\/p>

<\/p>

APPLICATION OF AI IN ACCOUNTING<\/strong><\/p>

Let’s take a look at how the capabilities of ChatGPT<\/mark><\/strong> can be utilized in the field of accounting and taxes. Let\u2019s break down the data workflow in these areas into four sections:<\/p>

  1. Data Collection<\/strong><\/li><\/ol>

    With ChatGPT, we can create intuitive and clear templates for collecting high-quality data more quickly. For example, data can be gathered through forms, questionnaires, or readable and easy-to-fill tables.<\/p>

    1. Data Cleaning<\/strong><\/li><\/ol>

      In this step, AI-generated scripts can help remove duplicates, fill in missing information, standardize formats, and remove special characters that are not accepted by our systems in subsequent stages.<\/p>

      1. Data Processing<\/strong><\/li><\/ol>

        After collecting and cleaning the data, AI-generated scripts can assist us in merging data, creating financial reports, or adjusting files to the structure required by our financial accounting system.<\/p>

        1. Verification<\/strong><\/li><\/ol>

          The increasing complexity of regulations means that more and more transactions require more detailed analyses. AI-generated scripts can assist accountants by automatically checking for threshold exceedances or compliance with specified conditions. With the help of scripts, we can receive a list of transactions that require further in-depth analysis.<\/p>

          Practical Example \u2013 Convenient Data Merging<\/strong><\/strong><\/p>

          Let’s demonstrate with a simple example how we can use ChatGPT to create a script in Visual Basic for Applications (VBA) that merges data from multiple files. The task is as follows: combine data from dozens of files and tables into one file<\/mark><\/strong>. We have 24 files, 48 tables, and thousands of rows. Let’s work with ChatGPT to create a VBA script that will merge this data with a single click.<\/p>

          1. Defining Needs and Assumptions<\/strong><\/p>

          Let’s start by analyzing the process we want to automate and define our objectives. Describe this as precisely as possible.<\/p>

          • Combine multiple files saved in xlsx format, each containing 2 tabs with data, into one consolidated file for further analysis.<\/li>\n\n
          • The structure of the input files is consistent \u2013 they contain 2 tabs with 10-column tables. The table headers are in the first row.<\/li><\/ul>
            \"\"
            1. Input file structure<\/figcaption><\/figure><\/figure>
            • Use the tools already included in the Microsoft 365.<\/li>\n\n
            • The files to be merged are located in the directory [C:\\Examples\\1\\Input].<\/li>\n\n
            • The number and names of files change<\/li>\n\n
            • The tab names in the files do not change: “VAT naliczony” (Input VAT) and “VAT nale\u017cny” (Output VAT).<\/li>\n\n
            • Data merging will be performed periodically on a varying number of files.<\/li><\/ul>

              To increase the chances of receiving a fully functional script on the first response, it’s important to remember a few key things. First and foremost, precisely define the assumptions, input data structure, and expected output format.<\/strong><\/mark><\/p>

              2. Creating the Prompt<\/strong><\/p>

              Based on the collected information, we create a prompt, which is a query to ChatGPT. Clarify in the prompt that during table merging, we want the header names not to be duplicated and for the program to ask the user where to save the generated file. Remember that general and ambiguous terms can lead to less precise responses and decrease the chance of obtaining a satisfactory result.<\/p><\/blockquote>

              Here is the prompt along with the model’s response:<\/p>

                <\/mark><\/p>

              <\/p>

              <\/p>

              <\/p>

              MODEL RESPONSE<\/strong>

              To merge multiple XLSX files into a single summary file using VBA in Microsoft 365, we need to create a macro that meets the stated objectives. Below is the detailed VBA code that accomplishes this goal:

              1. Open Excel and go to the VBA editor (Alt + F11).
              2. Navigate to ThisWorkbook (double-click on “ThisWorkbook” in “VBAProject”).
              3. Paste the following code into ThisWorkbook


              Sub MergeFiles()
                  Dim FolderPath As String
                  Dim outputWorkbook As Workbook
                  Dim inputWorkbook As Workbook
                  Dim outputSheet1 As Worksheet
                  Dim outputSheet2 As Worksheet
                  Dim inputSheet1 As Worksheet
                  Dim inputSheet2 As Worksheet
                  Dim FileName As String
                  Dim LastRow As Long
                  Dim lastRowOut1 As Long
                  Dim lastRowOut2 As Long
                  Dim okresVAT As String
                  Dim SavePath As String
                  Dim i As Integer
                
                  ‘ Path to folder with input files
                  FolderPath = “C:\\Przyk\u0142ady\\1\\Input\\”
                
                   ‘ Create a new output workbook
                  Set outputWorkbook = Workbooks.Add
                  Set outputSheet1 = outputWorkbook.Sheets(1)
                  outputSheet1.Name = “VAT naliczony”
                  Set outputSheet2 = outputWorkbook.Sheets.Add(After:=outputWorkbook.Sheets(1))
                  outputSheet2.Name = “VAT nale\u017cny”
                
                  ‘ Downloading a list of files in a folder
                  FileName = Dir(FolderPath & “*.xlsx”)
                
                  ‘ Initialization of header column flag
                  Dim headerSet As Boolean
                  headerSet = False
                
                  ‘ Processing of each file
                  Do While FileName <> “”
                      Set inputWorkbook = Workbooks.Open(FolderPath & FileName)
                    
                      ‘ Downloading bookmarks from the input file
                      Set inputSheet1 = inputWorkbook.Sheets(“VAT naliczony”)
                      Set inputSheet2 = inputWorkbook.Sheets(“VAT nale\u017cny”)
                    
                      ‘ Set headers in the output file (if not already set)
                      If Not headerSet Then
                          For i = 1 To 10
                              outputSheet1.Cells(1, i).Value = inputSheet1.Cells(1, i).Value
                              outputSheet2.Cells(1, i).Value = inputSheet2.Cells(1, i).Value
                          Next i
                          outputSheet1.Cells(1, 11).Value = “Okres VAT”
                          outputSheet2.Cells(1, 11).Value = “Okres VAT”
                          headerSet = True
                      End If
                    
                      ‘ Copying data from the tab “VAT naliczony”
                      LastRow = inputSheet1.Cells(inputSheet1.Rows.Count, 1).End(xlUp).Row
                      lastRowOut1 = outputSheet1.Cells(outputSheet1.Rows.Count, 1).End(xlUp).Row + 1
                      inputSheet1.Range(“A2:J” & LastRow).Copy Destination:=outputSheet1.Range(“A” & lastRowOut1)
                      outputSheet1.Range(“K” & lastRowOut1 & “:K” & lastRowOut1 + LastRow – 2).Value = FileName
                    
                      ‘ Copying data from the tab “VAT nale\u017cny”
                      LastRow = inputSheet2.Cells(inputSheet2.Rows.Count, 1).End(xlUp).Row
                      lastRowOut2 = outputSheet2.Cells(outputSheet2.Rows.Count, 1).End(xlUp).Row + 1
                      inputSheet2.Range(“A2:J” & LastRow).Copy Destination:=outputSheet2.Range(“A” & lastRowOut2)
                      outputSheet2.Range(“K” & lastRowOut2 & “:K” & lastRowOut2 + LastRow – 2).Value = FileName
                    
                      inputWorkbook.Close SaveChanges:=False
                      FileName = Dir
                  Loop
                
                  ‘ Question to the user about where to save the resulting file
                  With Application.FileDialog(msoFileDialogSaveAs)
                      .Title = “Zapisz plik wynikowy”
                      .FilterIndex = 1
                      If .Show = -1 Then
                          SavePath = .SelectedItems(1)
                          outputWorkbook.SaveAs FileName:=SavePath
                      End If
                  End With
                
                  outputWorkbook.Close SaveChanges:=True
                
                  MsgBox “The file was saved as ” & SavePath
               
              End Sub



              Explanation:
              Variable declaration:<\/strong> We declare variables needed to store paths, file names, workbooks and worksheets.  
              Retrieving the path and name of the output file:<\/strong> The user is asked for the location and name of the output file.  
              Creating a new output file:<\/strong> A new workbook is created with two sheets “VAT naliczony” and “VAT nale\u017cny”.   P<\/strong>
              rocessing files in a folder:<\/strong> The loop goes through all the XLSX files in the folder, opening them and copying the corresponding data to the output file.  
              Copying headers:<\/strong> Column headers are copied only once, from the first input file.  
              Copying data:<\/strong> Data is copied to the corresponding sheets in the output file, and the name of the source file is added to the eleventh column.
              Closes the files and saves the output file: <\/strong>The output file is saved to the location you selected.  

              Make sure you have the proper permissions to read and write files to the specified locations and that all files are as intended (i.e., have the correct sheets and headers).<\/mark><\/em><\/td><\/tr><\/tbody><\/table><\/figure><\/div><\/div><\/div>

              3. Verification<\/strong><\/p>

              Before entering your first code into an Excel workbook, you must first add a Developer tab to the Ribbon. To do this, you need to:<\/p>

              1. On the File tab, go to Options and Ribbon Customization.<\/li>\n\n
              2. In the Customizing Ribbon and Home tabs area, select the Developer checkbox.<\/li><\/ol>
                \"\"
                2. How to turn on developer mode<\/figcaption><\/figure><\/figure>

                After activating the developer’s tab, just use the Alt + F11 keyboard shortcut to open the VBA editor. Enter the code in the main editor window.<\/p>

                \"\"
                3. VBA Editor View<\/figcaption><\/figure><\/figure>

                After entering the code, we can close the VBA editor. To run the macro you should:<\/p>

                1. Select Developer Tab.<\/li>\n\n
                2. Click the Macros button<\/li>\n\n
                3. Select the appropriate macro name and click the run button.<\/li><\/ol>
                  \"\"
                  4. How to run a macro<\/figcaption><\/figure><\/figure>

                  <\/p>

                  After running the macro and specifying the location to save the file, I received the following message:<\/p>

                  \"\"
                  5. Confirmation of saving the final file <\/figcaption><\/figure><\/figure>

                  Let’s look at what our output file looks like:<\/p>

                  \"\"
                  6. Output file structure<\/figcaption><\/figure><\/figure>

                  The macro worked as expected and did not require any changes to the code, although this is not always the case. Sometimes the code generated by ChatGPT may be faulty and not work. In such cases, you should describe the error and ask the model to fix it. Usually, after a maximum of a few iterations, the error is located and corrected.<\/p>

                  We obtained an output file with combined data in two tabs related to VAT Incurred and VAT Due, with an additional column [VAT Period]. Providing detailed descriptions of our requirements and assumptions in the prompt, including information about paths, tab names, and file structure, allowed us to obtain a working macro.<\/p>

                  4. Adding more functionalities<\/strong><\/p>

                  After merging the files, we can add further elements to automate the process. For example, ChatGPT can create a script that generates a file with a list of transactions that meet the criteria we set. Let’s discuss this using the example of transactions subject to the split payment mechanism. VAT taxpayers are required to apply this mechanism if:<\/p>

                  1. They sell or purchase “sensitive” goods or services, which are listed in Annex 15 to the VAT Act, and simultaneously,<\/li>\n\n
                  2. The total invoice amount, i.e., the gross value of the entire invoice, exceeds 15 000 PLN.<\/li><\/ol>

                    The split payment mechanism must be applied if even one item on the invoice, whose total gross value exceeds 15 000 PLN, concerns sensitive goods or services.<\/p>

                    To get a list of transactions that meet the above conditions, we could define in the prompt when a given transaction should be included. Looking at the file structure, we could specify that we want to receive items that meet the following conditions for individual rows:<\/code><\/pre>
                    1. In column G, the value equals 1 (The transaction is marked with GTU code 02, which covers sensitive goods).<\/li>\n\n
                    2. The sum of the values of items in columns from I to J is greater than 15 000 PLN.<\/li><\/ol>
                      We can apply a similar approach to creating scripts that verify and search for items that meet other conditions specified by us.<\/p>
                      SUMMARY<\/strong><\/p>
                      If you are just starting your adventure with creating macros, it is worth starting with simple examples and gradually moving on to more complex tasks. Maybe you want the macro to create a new file with selected tabs and contain values instead of formulas? Or do you need a macro that copies data from one file to another? Or maybe you want to automatically create a PDF file and save it in a specified location? The potential of generative artificial intelligence in accounting is enormous. Its incredible advantage is that we can get solutions perfectly tailored to our needs. And all of this by having a conversation in natural language, as if we were asking a more experienced colleague to perform a task, not an AI model. Something that was rather the domain of science fiction movies not long ago is now within our reach.<\/p>","protected":false},"excerpt":{"rendered":"
                      See how AI can be used to create macros in Excel and save valuable time.<\/p>\n","protected":false},"author":16,"featured_media":2152,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"rank_math_lock_modified_date":false,"footnotes":""},"categories":[791],"tags":[],"popular":[],"difficulty-level":[38],"ppma_author":[357],"class_list":["post-2332","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ai-how-to","difficulty-level-medium"],"acf":[],"authors":[{"term_id":357,"user_id":16,"is_guest":0,"slug":"mpezowicz","display_name":"Marcin Pezowicz","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/39181f1c2712633923d1f9b67408c9f6459e89a5fd7106887cc359b52cf8a433?s=96&d=mm&r=g","first_name":"Marcin","last_name":"Pezowicz","user_url":"","job_title":"","description":"BPO Consultant. Z wykszta\u0142cenia ksi\u0119gowy oraz automatyk i robotyk. Specjalizuje si\u0119 w usprawnianiu proces\u00f3w z obszaru ksi\u0119gowo\u015bci i podatk\u00f3w."}],"_links":{"self":[{"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/posts\/2332","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/users\/16"}],"replies":[{"embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/comments?post=2332"}],"version-history":[{"count":34,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/posts\/2332\/revisions"}],"predecessor-version":[{"id":5194,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/posts\/2332\/revisions\/5194"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/media\/2152"}],"wp:attachment":[{"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/media?parent=2332"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/categories?post=2332"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/tags?post=2332"},{"taxonomy":"popular","embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/popular?post=2332"},{"taxonomy":"difficulty-level","embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/difficulty-level?post=2332"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/haimagazine.com\/en\/wp-json\/wp\/v2\/ppma_author?post=2332"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}