Relative path in database query connection string

Hi All,

I would really appreciate your help on this.

I have created a spreadsheet that works out all of our quotations. There are many different lines of items that could be included in each job. However, only a few are needed for each different quotation. I have created a sumaary page where all the data is chanelled and then on the same sheet used a Microsoft Query table on the elliminating the blank quanitity lines. What I understand is that it pulls the data from the query using a connection string. It all works like a dream. However, each time I need to do a new quotation, I save as the blank master to the new job name, but the query still pulls from the original file. After some googling, I figured out that it may be to do with Relative and absolute paths. Can someone show me how to let me 'save as'  and change the connection string automatically to pull the data from the new file.

Thanks
alan1234Asked:
Who is Participating?
 
MakriniCommented:
no - I mean like..
Sub changeit()

With Sheets("Tender Works").Range("B13").Listobject.QueryTable

    startpoint = (InStr(.Connection, "DBQ"))
    endpoint = InStr(startpoint, .Connection, ";")
    fullpath = Mid(.Connection, startpoint, endpoint - startpoint)
    endpoint2 = InStrRev(fullpath, "\")
    
    thepath = Left(fullpath, endpoint2)
    
    .Connection = Replace(.Connection, thepath, ActiveWorkbook.Path)

End With

End Sub

Open in new window

0
 
MakriniCommented:
What version of Excel?

  The simplest solution is to have the querytable ask you for the file each time
0
 
alan1234Author Commented:
2007

How would I do this - and more importantly could users other than me do this
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
MakriniCommented:
Try right clicking somewhere in the querytable, choose data range properties - and check the "prompt for filename on refresh" option

Then it should ask you every time you refresh.

There are other options if you are looking for a relative path - I actually use a Workbook open event - and change the querytable connection string directly using the path of the saved workbook...
0
 
alan1234Author Commented:
Cant see that  - I right click and go to Table>Extrnal Data Properties but it doesn't have that option there
0
 
MakriniCommented:
hmm - maybe it's just on text file querytables...

Can you run a debug.print(Activecell.querytable.connection) and copy the connection string from your VBA Immediate window?

I can create a replace command to directly change the connection string
0
 
alan1234Author Commented:
Makrini - Thank you very much for your attention to my problem.

I am really new to Query tables, macros and the like, so yuo have now lost me. I just don't see the point of using tables like this if you can't 'save as' without losing the connection. There has to be an easy way that once I give this package to our estimators they can just 'save as' the master file to do each new quote. I have read about making the string pull from the 'filename.xlsm' and not the name - but I would need help on this.

Thanks once again

0
 
MakriniCommented:
Whilst one of the cells in the querytable is selected...

If you press alt-f11 to get into VBA
Press ctrl-g to activate your "immediate" window
type
 ?Activecell.Querytable.connection

 - and copy the text that appears into this forum for me

(and I agree - I use querytables and data cubes etc for the 5 different locations I do reporting for.  I created custom solutions as a workaround)
0
 
alan1234Author Commented:
I did as you said and it came up with 'Run Time error' and 'Application defined or object-defined error.
0
 
MakriniCommented:
can you post your workbook?

It should definately give you a response unless the activecell is not on your querytable
0
 
alan1234Author Commented:
It has lots of personal info on it so rather not.

What is the replace command you mentioned earlier

0
 
MakriniCommented:
In VBA its

Replace(Activecell.querytable.connection,stringyouwanttoreplace,stringyouwant)

The trick is to find out what is already there and adjust the path.

  Would be easier for me to give the exact if you were able to get the connection string from the immediate window...

  1. It is a query table right - not a pivot table?

  2. You did actually have a cell in the querytable selected when you ran the command (exactly as I typed) in the immediate window?  - the selected cell MUST have the refresh option when you right click - otherwise the command I sent you will give the error you described.
0
 
alan1234Author Commented:
I got the following from going into connection properties>definition

The Connection String is:

DSN=Excel Files;DBQ=C:\Documents and Settings\alan\Desktop\Quotations master with macros.xlsm;DefaultDir=C:\Documents and Settings\alan\Desktop;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Yes it is a query table
0
 
MakriniCommented:
Here is a Macro you should be able to use.  Assign it to a button (or a workbook open event)

  Change "Sheet1" and "A1" to the correct locations of your querytable
Sub changeit()

With Sheets("Sheet1").Range("A1").QueryTable

    startpoint = (InStr(.Connection, "DBQ"))
    endpoint = InStr(startpoint, .Connection, ";")
    fullpath = Mid(.Connection, startpoint, endpoint - startpoint)
    endpoint2 = InStrRev(fullpath, "\")
    
    thepath = Left(fullpath, endpoint2)
    
    .Connection = Replace(.Connection, thepath, ActiveWorkbook.Path)

End With

End Sub

Open in new window

0
 
alan1234Author Commented:
Ran it and came up with the same 'run time error 1004' as earlier
0
 
MakriniCommented:
Then the cell you are pointing at is not likely to contain the querytable
0
 
MakriniCommented:
i.e  

With Sheets("Sheet1").Range("A1").QueryTable

where that is pointing
0
 
alan1234Author Commented:
With Sheets("Tender Works").Range("B13").QueryTable

Assigned it to a button and comes up with same message

(I even tried the range i.e B13:G13 but that got the same error. But the table goes bigger and smaller depending on the size of the quote. If I post the workbook will we be able to delete it straightaway?
0
 
MakriniCommented:
I don't know whether this site can do that.  Probably not a good idea to publish it in such a public place if it contains confidential information.

  You could temporarily upload somewhere and once I grab it you could remove from where you loaded it....
0
 
MakriniCommented:
I'm assuming if you right click on sheet "Tenderworks" in cell "B13" you get the "refresh" option?
0
 
alan1234Author Commented:
Yes
0
 
MakriniCommented:
in your VBA immediate window (alt f-11 then alt-g)

type this

?Sheets("Tender Works").Range("B13").QueryTable.connection

and tell me if you get the error...
0
 
alan1234Author Commented:
Yes
0
 
MakriniCommented:
try

?Sheets("Tender Works").Range("B13").Value
0
 
alan1234Author Commented:
Returned

Head (first column title)

0
 
MakriniCommented:
Aha!  - Apologies - I didn't realise you were using Excel 2007 - and I didn't realise the command has changed slightly...

change that line for...

With Sheets("Tender Works").Range("B13").Listobject.QueryTable
0
 
alan1234Author Commented:
Makrini,

I had to leave the office, so am home now, but don't have 2007 at home. Will hook up again on monday if that is ok.

I really appreciate your time today, your expertise is really impressive and hopefully we can sort this out next week.

Thanks once again

Alan
0
 
MakriniCommented:
It should work for you.  And this is never a problem.  

I only know what I am talking about because I am on excel all day every day - and I have spent a lot of time on forums like this listening to people better than me.

  Let me know how it goes
0
 
alan1234Author Commented:
Tried
With Sheets("Tender Works").Range("B13").Listobject.QueryTable and got an error saying 'unexpected end with'

0
 
MakriniCommented:
can you post the entire code you are using?
0
 
alan1234Author Commented:
With Sheets("Tender Works").Range("B13").Listobject.Query
0
 
alan1234Author Commented:
Sorry,

Sub changeit()
 
With Sheets("Tender Works").Range("B13").QueryTable
 
    startpoint = (InStr(.Connection, "DBQ"))
    endpoint = InStr(startpoint, .Connection, ";")
    fullpath = Mid(.Connection, startpoint, endpoint - startpoint)
    endpoint2 = InStrRev(fullpath, "\")
     
    thepath = Left(fullpath, endpoint2)
     
    .Connection = Replace(.Connection, thepath, ActiveWorkbook.Path)
 
End With
 
End Sub
0
 
MakriniCommented:
try my code above instead
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.