Tweaking Import References

[bhujanga]

I'm importing several groups of data in a single spreadsheet to make a series of graphs. At this point there is some question as to exactly where the database will reside when we're finished getting this ready. So, as a s result, it would be nice to be able to see the code for the import references, so I can just tweak the paths, rather than re-doing all of the imports from scratch. Is there a place to access and edit that information? I don't seem to be able to find it.
Thanks.

 

[SkipVought]

hi,

What version Excel?

How are you importing: Text Import, MS Query, other?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bhujanga]

It's 2010.
I'm importing an query from Access.

 

[SkipVought]

If you select in the QueryTable resultset to activate the Table Tools Design Tab, you will see the External Data Table > Properties.

In the Extrenal Data Properties window you can access the Connection Properties and on the Definition Tab, are the Connection String and SQL.

You can try to change the connection there. I never did it there, but I often do change the connection string via VBA code, so I can advise if your manual change does not "stick."

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bhujanga]

I tried the method you suggested and it seems to work fine. I would be interested though in the VBA way to do it as it seems like it might be more expeditious if I need to change it around more frequently. And I presume that using this approach I could even program it to just ask the user for the path, if it needed to be changed, without them ever needing to see the code. I can envision cases where this might be useful.

Thanks.

 

[SkipVought]

Assuming you have at least ONE ListObject QueryTable on your sheet

with YourSheetObject.ListObjects(1).QueryTable
  Debug.Print .Connection
  Debug.Print .CommandText
end With

This will write the connection string and the SQL to the Immediate Window in the VB Editor.

Copy the Connection String into code like

Sub ChangeString()
    Dim sPath As String, sDB As String, sConn As String
    
    sPath = ThisWorkbook.Path
    
    sDB = "Database1.accdb"
    
    sConn = "ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\ii36250\My Documents\Database1.accdb;DefaultDir=C:\Documents and Settings\ii36250\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    With Sheet1.ListObjects(2).QueryTable
      Debug.Print .Connection
      Debug.Print .CommandText
    End With

End Sub

and substitute like this...

Sub ChangeString()
    Dim sPath As String, sDB As String, sConn As String
    
    sPath = ThisWorkbook.Path
    
    sDB = "Database1.accdb"
    
    sConn = "ODBC;DSN=MS Access Database;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    With Sheet1.ListObjects(2).QueryTable
      .Connection = sConn
      .Refresh False
    End With

End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]