none
Dynamic Data Connection

    Question

  • Hello

    My workbook will import data from an external csv file. I created a connection (to import "From Text") and it works perfect. The issue is, csv files are going to be named with dates (something like somefilename_02AUG13.csv) and I want to refresh my workbook with the current dated csv file.

    Is there a way to create a dynamic connection to instruct Excel which file to use?

    Thanks - Mukesh


    Thanks - Mukesh

    Friday, August 02, 2013 8:05 AM

Answers

  • You could put something like the following in the ThisWorkbook section of the VBE...

    '------------------------------------------------------------------------------
    ' Module    : ThisWorkbook
    ' Author    :
    ' Date      :
    ' Purpose   :
    ' References:
    '------------------------------------------------------------------------------
    '
    '''''''''''''''''''
    ' OPTIONS
    '
    Option Base 0
    Option Compare Binary
    Option Explicit
    'Option Private Module
    '
    '''''''''''''''''''
    ' DECLARATIONS
    '
    Private m_ConnectionString As String
    Private Const CONNECTION_PATH As String = "C:\Temp\"
    Private Const CONNECTION_FILE As String = "somefilename"
    Private Const CONNECTION_EXTN As String = ".csv"
    '
    '------------------------------------------------------------------------------
    ' Procedure : UpdateConnection
    ' Author    :
    ' Date      :
    ' Purpose   :
    ' References:
    '------------------------------------------------------------------------------
    '
    Sub UpdateConnection()
    '''''''''''''''''''
    ' HANDLE ERRORS
    '
      On Error GoTo PROC_ERR
    '''''''''''''''''''
    ' PROCEDURE
    '
      Dim ws As Worksheet
      Dim qt As QueryTable
      
      'Set the New Connection
      Let m_ConnectionString = "TEXT;" & CONNECTION_PATH & CONNECTION_FILE & "_" & VBA.Format$(VBA.Date, "DDMMMYY") & CONNECTION_EXTN
      
      'Loop through each WorkSheet in this Workbook
      For Each ws In ThisWorkbook.Worksheets
        'Loop through each QueryTable in each WorkSheet
        For Each qt In ws.QueryTables
          'If you find a QueryTable with a Name that contains the CONNECTION_FILE string
          If InStr(1, qt.Name, CONNECTION_FILE, vbTextCompare) Then
            'Set the Connection string to the New Connection
            qt.Connection = m_ConnectionString
          Else
            'If not, do nothing
          End If
        Next qt
      Next ws
    '''''''''''''''''''
    ' EXIT PROCEDURE
    '
    PROC_EXIT:
      Exit Sub
    '''''''''''''''''''
    ' ERROR HANDLER
    '
    PROC_ERR:
      MsgBox "[" & Err.Number & "] " & Err.Description, vbCritical + vbOKOnly, "UpdateConnection() Error"
      Resume PROC_EXIT
    '''''''''''''''''''
    End Sub

    Hope that helps.


    Regards, Gabriel Hargens

    Friday, August 02, 2013 11:04 PM

All replies

  • insert below function in a cell and you will get the file named with today's date.

    =+TEXT(TODAY(),"""_""ddmmmyy"".csv""")

    If you tell us how the connection string looks we can give you a function for updating directly in the connection string.

    Basically lot of ways you can connect to external files and their connection string is also totally diff.

    To get the connection string.

    Select any cell of imported area - >Data Tab->Properties->In Extreme right a single button with hand icon->definition tab


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 02, 2013 9:45 AM
  • I am using Data tab > From Text and pointing to the csv file I am willing to import. I tried to see if there is any connection string associated with this connection, and there are none.

    Thanks - Mukesh

    Friday, August 02, 2013 10:06 AM
  • You could put something like the following in the ThisWorkbook section of the VBE...

    '------------------------------------------------------------------------------
    ' Module    : ThisWorkbook
    ' Author    :
    ' Date      :
    ' Purpose   :
    ' References:
    '------------------------------------------------------------------------------
    '
    '''''''''''''''''''
    ' OPTIONS
    '
    Option Base 0
    Option Compare Binary
    Option Explicit
    'Option Private Module
    '
    '''''''''''''''''''
    ' DECLARATIONS
    '
    Private m_ConnectionString As String
    Private Const CONNECTION_PATH As String = "C:\Temp\"
    Private Const CONNECTION_FILE As String = "somefilename"
    Private Const CONNECTION_EXTN As String = ".csv"
    '
    '------------------------------------------------------------------------------
    ' Procedure : UpdateConnection
    ' Author    :
    ' Date      :
    ' Purpose   :
    ' References:
    '------------------------------------------------------------------------------
    '
    Sub UpdateConnection()
    '''''''''''''''''''
    ' HANDLE ERRORS
    '
      On Error GoTo PROC_ERR
    '''''''''''''''''''
    ' PROCEDURE
    '
      Dim ws As Worksheet
      Dim qt As QueryTable
      
      'Set the New Connection
      Let m_ConnectionString = "TEXT;" & CONNECTION_PATH & CONNECTION_FILE & "_" & VBA.Format$(VBA.Date, "DDMMMYY") & CONNECTION_EXTN
      
      'Loop through each WorkSheet in this Workbook
      For Each ws In ThisWorkbook.Worksheets
        'Loop through each QueryTable in each WorkSheet
        For Each qt In ws.QueryTables
          'If you find a QueryTable with a Name that contains the CONNECTION_FILE string
          If InStr(1, qt.Name, CONNECTION_FILE, vbTextCompare) Then
            'Set the Connection string to the New Connection
            qt.Connection = m_ConnectionString
          Else
            'If not, do nothing
          End If
        Next qt
      Next ws
    '''''''''''''''''''
    ' EXIT PROCEDURE
    '
    PROC_EXIT:
      Exit Sub
    '''''''''''''''''''
    ' ERROR HANDLER
    '
    PROC_ERR:
      MsgBox "[" & Err.Number & "] " & Err.Description, vbCritical + vbOKOnly, "UpdateConnection() Error"
      Resume PROC_EXIT
    '''''''''''''''''''
    End Sub

    Hope that helps.


    Regards, Gabriel Hargens

    Friday, August 02, 2013 11:04 PM
  • Thanks for responses. I am aware that we could easily do this using VBA. I was curious to know if there any option to do without writing any macro (so that we can save it as .xlsx).

    Can I assume there are no way to achieve this, at least in Excel 2010? If yes, isn't it going to be a useful feature for future Excel versions?


    Thanks - Mukesh

    Monday, August 05, 2013 1:40 AM