How do I use VBA and variables to create dynamic import connections?

[cmissal]

I have been building a dashboard that, at present, requires strict path/filenames for import files. When I trigger the import refresh VBA code, I want it to update the connection string to use the value stored in the cell referenced by a Name as the path/file. (I am importing .xlsx file type)


Ideally, I would like to transition directly to DDE to the online databases, but for now, I just want to be able to use Excel Names to identify the cell value where I store the path/file name to import.

 

[cmissal]

Bump.


If no one has a quick solution, I would at least be greatful for some direction as to where I should begin my research. thanks!

 

[Hui]

Cmissal


When you say Connection String are you referring to cells that link directly to other workbooks or to Data Connections?


Can you post the sample of VBA code you want to link to a cells values

 

[cmissal]

Hi Hui! I didn't see your response previously.


I am referring to a "Data Connection" to another workbook, not a cell reference to another workbook. Basically, I have tables in my 'dashboard' workbook and part of the instructions have users trigger VBA events by clearing contents of an instruction cell which has the VBA perform the portion of the code. One portion (below) just simply does a 'refresh all' of the manually defined data connections.


I would like to have the Data Connection 'connection string' to be updated based on a value in a cell (named reference) before the refresh takes place. This will allow the user to specify where the external file is located, in the event the location has changed. (i.e. if different users utilize this dashboard tool their paths will likely be different).


CODE:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Range("adminImportingResetFlag").Value = "" Then

'Refresh data from pre-defined external connections

Range("adminImportingResetFlag").Value = "Refreshing Imported Data..."

Application.ScreenUpdating = False

'Application.Calculation = xlManual

Application.EnableEvents = False

ActiveWorkbook.RefreshAll

'this next command doesn't appear to be working... possibly too close to the end of the refresh command

'Range("Data_Current_Wk_Actions!2:2").Delete Shift:=xlUp

'Refresh Milestone/Deliverable Advanced Filter

Application.ScreenUpdating = True

'Application.Calculation = xlManual

Application.EnableEvents = True

....

....

 

[wosnky]