[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Relative path in database query connection string

Posted on 2009-12-18
34
Medium Priority
?
851 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:alan1234
  • 18
  • 15
33 Comments
 
LVL 10

Expert Comment

by:Makrini
ID: 26080551
What version of Excel?

  The simplest solution is to have the querytable ask you for the file each time
0
 

Author Comment

by:alan1234
ID: 26080582
2007

How would I do this - and more importantly could users other than me do this
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26080651
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:alan1234
ID: 26080711
Cant see that  - I right click and go to Table>Extrnal Data Properties but it doesn't have that option there
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26080765
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
 

Author Comment

by:alan1234
ID: 26080912
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
 
LVL 10

Expert Comment

by:Makrini
ID: 26080959
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
 

Author Comment

by:alan1234
ID: 26081023
I did as you said and it came up with 'Run Time error' and 'Application defined or object-defined error.
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26081041
can you post your workbook?

It should definately give you a response unless the activecell is not on your querytable
0
 

Author Comment

by:alan1234
ID: 26081194
It has lots of personal info on it so rather not.

What is the replace command you mentioned earlier

0
 
LVL 10

Expert Comment

by:Makrini
ID: 26081226
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
 

Author Comment

by:alan1234
ID: 26081275
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
 
LVL 10

Expert Comment

by:Makrini
ID: 26081411
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
 

Author Comment

by:alan1234
ID: 26081536
Ran it and came up with the same 'run time error 1004' as earlier
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26081543
Then the cell you are pointing at is not likely to contain the querytable
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26081556
i.e  

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

where that is pointing
0
 

Author Comment

by:alan1234
ID: 26081606
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
 
LVL 10

Expert Comment

by:Makrini
ID: 26081636
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
 
LVL 10

Expert Comment

by:Makrini
ID: 26081657
I'm assuming if you right click on sheet "Tenderworks" in cell "B13" you get the "refresh" option?
0
 

Author Comment

by:alan1234
ID: 26081681
Yes
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26081700
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
 

Author Comment

by:alan1234
ID: 26081795
Yes
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26081856
try

?Sheets("Tender Works").Range("B13").Value
0
 

Author Comment

by:alan1234
ID: 26081956
Returned

Head (first column title)

0
 
LVL 10

Expert Comment

by:Makrini
ID: 26082046
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
 

Author Comment

by:alan1234
ID: 26082548
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
 
LVL 10

Expert Comment

by:Makrini
ID: 26084422
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
 

Author Comment

by:alan1234
ID: 26094757
Tried
With Sheets("Tender Works").Range("B13").Listobject.QueryTable and got an error saying 'unexpected end with'

0
 
LVL 10

Expert Comment

by:Makrini
ID: 26103276
can you post the entire code you are using?
0
 

Author Comment

by:alan1234
ID: 26103325
With Sheets("Tender Works").Range("B13").Listobject.Query
0
 
LVL 10

Accepted Solution

by:
Makrini earned 1000 total points
ID: 26103343
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
 

Author Comment

by:alan1234
ID: 26103367
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
 
LVL 10

Expert Comment

by:Makrini
ID: 26103431
try my code above instead
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question