changing data source in Excel query

[Maya]

I have a problem with Excel Query and changing data source. I use dbf file as a data source for my query and I need to be able to change the name or location of the data source. How do I do that? In Lotus123 I could just rename or remove the file from the directory and when I try to run query I was asked if I wanted to change data source. this is not what happens in Excel. Please help.

[Mark W.]

If you're using XL2000 edit the DSN using the Microsoft Script Editor (Tools | Macro | Microsoft Script Editor or Alt+Shift+F11).

[Maya]

Thank you Mark. But I am using Excel97. Any suggestions?

[Jack in the UK]

Is this being done by

VBA Script
Manual
Pivot table
???

[Jack in the UK]

Erm

Thinking I once wrote an ODBC script a while back for a company I worked for which had fussy DSN and data source over X400 links and Excel 97

Ill did it out!

[Mark W.]

On 2002-06-02 14:14, Maya wrote:
Thank you Mark. But I am using Excel97. Any suggestions?

Did you setup a File DSN using the Microsoft ODBC Admininistrator or did you just choose the generic "dBase Files*" data source when you created your MS Query?

[ This Message was edited by: Mark W. on 2002-06-02 14:31 ]

[Jack in the UK]

This is more current that gives live outstanding finance details, and sill usable where i am now so about 18 months old.

My name is the critical parts, you need to edit but this will work. If the DSN is correct

Sub ISO_DownLoad() ' This is ODCB Live data to Excel
' & Sets up new WorkSheet, names the WorkSheet with todays date & Time
'Each WorkBook is saved Seperatly
'JACKintheUK sets up varables to procedure

United_Colours_of_BRIXTON = Format$(Now, "d mmm yyyy hh~mm~ss")

If Worksheets("Index").Range("G14").Value = "Y" Then
Application.Calculation = xlManual
Application.CalculateBeforeSave = True

Application.DefaultSaveFormat = xlExcel5Workbook
'Application.Run Range("AUTOSAVE.XLA!mcs02.OnTime")
'ActiveWorkbook.SAVE
Sheets.Add
With Selection
ActiveSheet.Name = United_Colours_of_BRIXTON
End With
End If


‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK

Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Kopen3 Live;UID=bwo;;SERVER=ufsvr20;DBNAME=KO3LIVE;LUID=bwo;", _
Destination:=Range("A2"))
.Sql = Array( _
"SELECT ""Sales Ledger Transactions Co 01"".""Customer Code"", ""Sales Ledger Transactions Co 01"".""Invoice Number"", ""Sales Ledger Transactions Co 01"".""Invoice Date"", ""Sales Ledger Transactions Co 01"".""Invoi" _
, _
"ce Value (Curr)"", ""Sales Ledger Transactions Co 01"".Status" & Chr(13) & "" & Chr(10) & "FROM ""Sales Ledger Transactions Co 01"" ""Sales Ledger Transactions Co 01""" & Chr(13) & "" & Chr(10) & "WHERE (""Sales Ledger Transactions Co 01"".""Invoice Value (Curr)""<>0" _
, _
") AND (""Sales Ledger Transactions Co 01"".Status<>'P')" & Chr(13) & "" & Chr(10) & "ORDER BY ""Sales Ledger Transactions Co 01"".""Customer Code"", ""Sales Ledger Transactions Co 01"".""Invoice Date""" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK
‘JACKintheUK JACKintheUK JACKintheUK JACKintheUK JACKintheUK

Columns("D:D").Select
Selection.Style = "Currency"
Range("A1").Select

Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="-1"
Selection.FormatConditions(1).Font.ColorIndex = 6
Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.Rows.AutoFit
Selection.Columns.AutoFit
End With

'Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("B2") _
', Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
'False, Orientation:=xlTopToBottom
'End Sub
'Worksheets(United_Colours_of_BRIXTON).Rows(1).Insert

Range("F1").Select
ActiveCell.FormulaR1C1 = "ISO"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Credir ISO"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0, RC[-2], ""0.00"")"
Range("F2").Select
Selection.Copy
Range("D2").Select
Selection.End(xlDown).Select
Range("F2:F2345").Select
Range("F2345").Activate
ActiveSheet.Paste
Selection.End(xlUp).Select
Range("G2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-3]<0, RC[-3], ""0.00"")"
Range("G2").Select
Selection.Copy
Range("F2").Select
Selection.End(xlDown).Select
Range("G2:G2345").Select
Range("G2345").Activate
ActiveSheet.Paste
Selection.End(xlUp).Select
Application.CutCopyMode = False
Calculate


End Sub

'XXXXXXXXXXXXXXXXXXX
'Do figure works here
'XXXXXXXXXXXXXXXXXXXXX

[Jack in the UK]

Another i did this is recorded.. I recorded this to prove to a mate at work i can do this... thus the silly sub name.


Attribute VB_Name = "Module1"
Sub jackisstayingforever()
Attribute jackisstayingforever.VB_Description = "Macro recorded 15/09/00 by jack"
Attribute jackisstayingforever.VB_ProcData.VB_Invoke_Func = " n14"
'
'jackisstayingforever Macro
' Macro recorded 15/09/00 by jack
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Kopen3 Live;UID=emc;;SERVER=ufsvr20;DBNAME=KO3LIVE;LUID=emc;", _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT ""Sales Ledger Customer File Co 01"".""Address 01"", ""Sales Ledger Customer File Co 01"".""Address 02"", ""Sales Ledger Customer File Co 01"".""Address 03"", ""Sales Ledger Customer File Co 01"".""Address 04" _
, _
"""" & Chr(13) & "" & Chr(10) & "FROM ""Sales Ledger Customer File Co 01"" ""Sales Ledger Customer File Co 01""" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
End Sub

[Maya]

On 2002-06-02 14:27, Mark W. wrote:

Did you setup a File DSN using the Microsoft ODBC Admininistrator or did you just choose the generic "dBase Files*" data source when you created your MS Query?

[ This Message was edited by: Mark W. on 2002-06-02 14:31 ]

I went to Data, Get external data, Create new query,. I named and created a new data source and used it for my query. The problem is that I was working with the copy of database file on my local drive. Now I need to change the query to look in another drive on the network. Also, often I use query tables to extract data from files which names change every month. But I learned how to work around that(rename the file)
I appreciate your help. I am new to Excel and still cannot get Lotus123 out of my system.

[Jack in the UK]

DSN=Kopen3

Thiss ection should under your bespoke database link to the server and connect as required, if not the ICT Dept have configured this badly, would be my call, i have done these in a few different companys, and the open source is the link, in such

HTH