Common Data Source

[motilulla]

Hello Everyone,

I do not know how to explain in one word, actually what I need for example I have lottery data to analyse them and to find different results I have 20 workbook when result come I have to enter in each workbook and get analysis result

Is there any source where I can store result data and when I open each workbook get data from that source to avoid inserting results in each of workbook

Thanks and Regards,
Moti.

 

[Smitty]

You can set up a data import. Look at the Data tab-->Get External Data and choose the one that's appropriate for you.

 

[motilulla]

You can set up a data import. Look at the Data tab-->Get External Data and choose the one that's appropriate for you.

Thank you Smitty I Look at the Data tab-->Get External Data I found a window to select data form origin
1-dBASE Files*
2-Excel Files*
2-MS Access Database*
I did select and create source from Excel Files*

And then I enter the results in the source file when I open the destination file I find refreshed and Added the new data it from source file this works like a magic
Thank you for your help

When I create source my file was at Desktop I cut and paste in the hard disk c:
And when I open again destination file I cannot refresh data error source path
Perhaps

Please can you guide how I can change the path I have tried but could not find the way to do.


Thanks and Regards,
Moti.

 

[Smitty]

If you look at the Connections tab, you can change the query properties there.

 

[motilulla]

If you look at the Connections tab, you can change the query properties there.

Thank you Smitty

I do not find Connection property I am using excel version 2000 which is very old now do you think this version has Connections tab query properties. To change the path

Thanks and Regards,
Moti.

 

[Smitty]

Hmmm, in Excel 2000 try opening MSQuery. If the query properties are there, then you can change them.

 

[motilulla]

Hmmm, in Excel 2000 try opening MSQuery. If the query properties are there, then you can change them.

Hello Smitty, Ok when I click on tab modify query new window pop up with message “Assistant can not modify this query” if I accept ok open new window Microsoft query with one sub window (SLQ) fill with this text below

“SELECT `Results$`.Fecha, `Results$`.Dia, `Results$`.Jor, `Results$`.Temp, `Results$`.Resultados, `Results$`.F6, `Results$`.F7, `Results$`.F8, `Results$`.F9, `Results$`.F10, `Results$`.F11, `Results$`.F12, `Results$`.F13, `Results$`.F14, `Results$`.F15, `Results$`.F16, `Results$`.F17, `Results$`.F18, `Results$`.F19
FROM `Results$` `Results$`”

When I accept Ok blank window open with name “query from Excel Files”
I do not find any thing else

One thing more: when I open destination workbook
I find property tab and when I click on it at the top I find
Name: query from Excel Files

I imagine here I need to change? But I do not find any path

May be in this version it is not possible to change the path

Please suggest any other way if you find, I will also keep searching if get some thing I will come back to inform you

Thank you for you kind help

Regards,
Moti.

 

[Smitty]

Unfortunately, it's been a long time since I used 2000, and I don't even have access to it anymore, so I don't really know what to say. If you can see the SQL statement, then there should be somewhere to modify the path.

 

[motilulla]

Unfortunately, it's been a long time since I used 2000, and I don't even have access to it anymore, so I don't really know what to say. If you can see the SQL statement, then there should be somewhere to modify the path.

Hello Smitty, I find one of your post with same problem “Unable to update excel query with moved access file location”

First, put your Access file back where it was originally linked, then turn on the macro recorder and run your query.
Hope that helps, Smitty

I did exactly the same as you explained record the macro and it works fine only I have to change the path manually each time I move the file location

Sub Macro1()
   
   With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\DOCUMENTS AND SETTINGS\MOTIRAM\ESCRITORIO\1X2 Especial Count 1X2 Occurrences &  Force.xls;DefaultDir=C:\" _
        ), Array( _
        "DOCUMENTS AND SETTINGS\MOTILULLA\ESCRITORIO;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
        )), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT `Results$`.Fecha, `Results$`.Dia, `Results$`.Jor, `Results$`.Temp, `Results$`.`         Resultados`, `Results$`.F6, `Results$`.F7, `Results$`.F8, `Results$`.F9, `Results$`.F10, `Results$`.F11, " _
        , _
        "`Results$`.F12, `Results$`.F13, `Results$`.F14, `Results$`.F15, `Results$`.F16, `Results$`.F17, `Results$`.F18, `Results$`.F19" & Chr(13) & "" & Chr(10) & "FROM `Results$` `Results$`" _
        )
        .Name = "Consulta desde Excel Files"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

I think this is only the way can be adapted for the connection with excel query files

Thank you for you help

Regards,
Moti.