Changing connection string path to a dynamic path

Hi there,

I'm new to this forum and i'm in need of some help in some excel coding as I know nothing of it.

I have used record macro for a certain excel sheet to import data from one excel workbook into this particular workbook but this has hard coded the path (C:\Users\shweerasinghe\Desktop\New folder) in the connection sting. The code is as follows:

Sheets("Master Data-CMB Bench").Select
Cells.Select
Selection.Delete Shift:=xlUp
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Users\shweerasinghe\Desktop\New folder\CMB Bench Report.xlsx;DefaultDir=C:\Users\shweerasinghe\Desktop\N" _
), Array("ew folder;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("$A$1")).QueryTable

.CommandText = Array( _
"SELECT * FROM `C:\Users\shweerasinghe\Desktop\New folder\CMB Bench Report.xlsx`.`'Master Data$'`" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Users\shweerasinghe\Documents\My Data Sources\CMB Bench Report.xlsx 'Master Data$'.odc"
.ListObject.DisplayName = "Table_CMB_Bench_Report.xlsx__Master_Data"
.Refresh BackgroundQuery:=False
End With

When this same workbook is put on another machine, it throws an error because the specified path is wrong. Is there anyway that i could put a dynamic path here so that whoever opens it from their machine will be able to go ahead with the task without having to re-set the path themselves? It can't be done through the wizard as an error is thrown. I also can change the path manually and it will work fine but is there any variable or something that could be put into those specific areas where the path has been hard coded so that the path will be picked up automatically?

Please help...

Thanks in advance =)

Shevi

With the filedialog application the user can browse for the file himself and set the patch accordingly.

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

So sorry to ask this, how do i add this piece into the above code? I don't really know how to code or do anything related to coding =( tried a couple of ways earlier before posting and failed.

Thank you for all the help

You want to copy the whole sheet to a sheet in another workbook? Upload a file if you can with an explanation of what you want to happen if you can.

What I have done is, since I don't know how to write any kind of code, I have used the record macro feature and that produced the above code. I have a normal workbook which is a .xlsx and from that I import the data into a sheet in a macro enabled workbook. In the record macro, I have gone as follows:

Data tab > Existing Connections > Browse for more > Connect to new data source.odc > ODBC DSN > Excel Files > Select path of where this .xlsx file is

But the path to the .xlsx file has been hard coded as above. The macro works fine on my machine but the moment it is opened on another machine, it asks for the path and it throws an error once the path is selected. So what needs to happen is that if that path can be made dynamic, then when another user opens it on his machine, the path will be automatically picked up and for the user to only click the button and everything else will happen.

I tried changing the hard coded path on another machine manually and the excel worked perfectly without any flaw but the user will not know how to do that =( I have attached two files. Hope this explanation helps with the two documents

Thanks once again =)
Dummy Dash.xlsmCMB Bench Report.xlsx

This is the data connection file placed under My Documents\My Data Sources

This should do the trick. You can tie the macro to a button if you would like to.

http://office.microsoft.com/en-001/e...010236676.aspx

Book1.xlsm

Hi bullshot25,

It took me a while to understand what was happening - really not code-savy =( but I like the approach with a form but the only thing is that the user apparently just wants to click a button without having to select anything to get the data... He just doesn't want to be fiddling around he says... Is there no other way that the existing code could be modified or is there any other alternative that I could use where an import can be done with just a click? I'm willing to delete the record macro code and rewrite an import macro if it is possible with anyone's help... I hope you understand my dilemma... I have no idea of any code writing and the user needs me to do it when I don't...

Thank you so much for taking your precious time to write that code for me and I really and truthfully appreciate it =)

Please help...

Hi bullshot25,

I thought of something like this... since this record macro seems to be quite complicated in importing the data, can a simple import be done only to select the file that needs to be imported? In my actual file, I have two excels to import so if I can have two prompts to open up to select the two files, that would do the trick i guess? Do you think this will work? So then there wouldn't be a problem in sharing the file since those particular files that need to be imported needed to be selected by the user himself. (i.e. when the button is clicked, the prompts will open up to select the two files and the data would load and the rest of the code executed too). I see this as the simplest option... Please tell me if i'm wrong...

Sorry if I'm saying too many things... No idea in this area and really need help =)

Thanks once again.

I'm not sure if I understand. What exactly is wrong with the macro I wrote? I does what you explained in your post, no? The user selects the file and the import is done?

Sorry, it works fine, just that I selected the wrong one... It works fine but there's just one problem... Once the import is done, there are some calculations that are done on the dashboard page of the workbook and for that it requires a table. So in my original sheet when the import is done, it imports into a table with a static table name (i think that is a standard method in the import option), thereby having no need of a counter to define the table names. For example, the table it imports the data into at all times would be Table_CMB_Bench_Report.xlsx__Master_Data. This becomes the standard name when importing. Is there anyway to modify your code so that this could come in?

Thank you in advance and sorry for the confusion. Hope it is clear =)

Hi Bullshot25,

I tried your code in a different way using the record macro but the table name seems to be a barrier =( All the calculations depend on that... Each time the data is imported, the table name changes too if it is done manually =( Please help...

Hi bullshot25,

unfortunately the popping up of the message to select the file isn't required... The user just needs the file to automatically pick up the document and import the data onto the dashboard... is there anyway to do that?