Soru Excel Query Kaynak Adresini Hücreden Nasıl Alır?

smallburak · 30 Aralık 2022

Merhaba arkadaşlar,
Çalıştığım şirkette 20 kişilik ekibin kullandığı bir dosya var. Ekip eskiden başka bir dosyadan kopyala yapıştır yaparak dosyayı güncelliyordu. Otomatik güncelleme yapabilmek için Query oluşturdum ve makro yazdım, şimdi kendisi güncelliyor. Tabi öncesinde, kullanıcının daha önceden data kopyaladığı kaynak dosyayı ilgili klasöre yerleştirmesi gerekiyor. Ve benim de Query ayarlarından yeni kaynak yolunu tanımlamam gerekiyor. Bu kaynak yolu tanımlama olayını 1 defa yapmak yeterli. Sonrasında kullanıcı kaynak dosyayı, ismi aynı olacak şekilde yeni bir kaynak dosyayla değiştirince sorun olmuyor.

Dediğim gibi bu dosyayı 20 kişi kullanıyor ve her sene yeni projeler için kullanılacak. Ben ana dosyayı bir kullanıcının bilgisayarına kurduğum zaman data kaynak dosyasının yolu/adresi de doğal olarak değişiyor ve Query ayarlarından kaynak yolunu tanımlamam gerekiyor. Demem o ki kullanıcı, başka bir sekmedeki bir hücreye kaynak yolunu kendisi elle yazsa, Query de kaynak adresini bu hücreden çekse ve kurulum için bana ihtiyaç duymasa süper olacak. Var mı bunun bir yolu?

Şimdiden teşekkür ederim

smallburak · 1 Ocak 2023

Merhabalar,

Yabancı bir forumdan nasıl yapacağımı öğrendim

Sorun çözüldü.

Sizinle de paylaşayım. Query adresini girdiğim hücreye isim veriyorum ve Query advance ayarlarından kodlardaki adres yerine hücreye verdiğim ismi yazıyorum. Böylece query, kodlar sayesinde o hücreyi bularak kendini güncelliyor.

Tefo · 6 Mart 2023

Merhabalar,
Query adresini hücreye nasıl bağlıyorsunuz yardımcı olabilir misiniz?

emsey · 6 Mart 2023

Üstad Makro excel dosyası açılmadan mı sorguyu güncelliyor tam olarak makro nasıl çalışıyor örnek dosya yükleseniz hücreden kaynak adresi alma ve sorguyu otomatik yenilemeden istifade etsek

smallburak · 7 Mart 2023

Tefo' Alıntı:
Merhabalar,
Query adresini hücreye nasıl bağlıyorsunuz yardımcı olabilir misiniz?

Merhaba hocam. Bir hücreye tıkladığınız zaman sol üstte hücrenin adresi yazıyor ya hani, mesela A12.
Bu A12 adresini silip oraya isim verebiliyorsunuz. Mesela ben "FilePath" dedim. Daha sonra Query - Advance'e giderek oradaki query adresini silerek bu hücreyi belirttim. Kodları bulamazsanız haberleşelim, buradan yayınlayayım. Şu anda iş bilgisayarımda olduğu için ulaşamıyorum

smallburak · 7 Mart 2023

emsey' Alıntı:
Üstad Makro excel dosyası açılmadan mı sorguyu güncelliyor tam olarak makro nasıl çalışıyor örnek dosya yükleseniz hücreden kaynak adresi alma ve sorguyu otomatik yenilemeden istifade etsek

Hocam dosya şirkete özel bilgiler içerdiği için paylaşmam doğru olmaz ama buradan yardımcı olmaya çalışacağım. Makro birkaç parçadan oluşuyor. Önce SAP'den stok dosyasını ZC78 kullanarak indiriyor. (Onu da query olarak ana dosyaya ekledim). İhtiyacım olan başka excel kaynak dosyalarımı da ana dosyaya query olarak ekledim. Makro, bu query'lere tek tek gidiyor ve refresh tuşuna basıyor. Böylece ana dosya tamamen güncelleniyor. Kodlara ihtiyacınız varsa lütfen belirtin. Şu anda iş bilgisayarım yanımda olmadığı için ulaşamıyorum. Yarın uygun bir zaman bulup, istediğiniz bilgileri buradan yazabilirim.

Tefo · 7 Mart 2023

Hocam öncelikle geri dönüşünüz için teşekkür ederim. Ben aşağıdaki yerden değiştirmeye çalıştım fakat hata aldım. Dediğiniz gibi hücrenin birisine de FilePath olarak isim verdim. Bu kodlara nasıl aktarmam gerekiyor. Yönlendirebilir misiniz?
Fotoğrafta gözüken link içerindeki 1 olan klasör her ay kopyalanarak çoğaltılıyor. 2 3 4 diye yıl sonuna kadar devam edecek.

smallburak' Alıntı:
Merhaba hocam. Bir hücreye tıkladığınız zaman sol üstte hücrenin adresi yazıyor ya hani, mesela A12.
Bu A12 adresini silip oraya isim verebiliyorsunuz. Mesela ben "FilePath" dedim. Daha sonra Query - Advance'e giderek oradaki query adresini silerek bu hücreyi belirttim. Kodları bulamazsanız haberleşelim, buradan yayınlayayım. Şu anda iş bilgisayarımda olduğu için ulaşamıyorum

smallburak · 7 Mart 2023

Tefo' Alıntı:
Hocam öncelikle geri dönüşünüz için teşekkür ederim. Ben aşağıdaki yerden değiştirmeye çalıştım fakat hata aldım. Dediğiniz gibi hücrenin birisine de FilePath olarak isim verdim. Bu kodlara nasıl aktarmam gerekiyor. Yönlendirebilir misiniz?
Fotoğrafta gözüken link içerindeki 1 olan klasör her ay kopyalanarak çoğaltılıyor. 2 3 4 diye yıl sonuna kadar devam edecek.
Ekli dosyayı görüntüle 243427

Tekrar merhaba

Query Advance kodları aşağıdaki gibidir.

Excel dosyası çekecekseniz:

Kod:

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FileName = Excel.CurrentWorkbook(){[Name="FileName1"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),
    #"FP Report_Sheet" = Source{[Item="FP Report",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"FP Report_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PO FPC", Int64.Type}, {"Loc", type text}, {"Initiative", type text}, {"PI FPC", Int64.Type}, {"PO Description", type text}, {"Prod Site Y/N", type text}, {"Dep Demand", type text}, {"SMO", type text}, {"Segment", type text}, {"Customer Conversion Type", type text}, {"P&G Conversion Type", type text}, {"In/Out Final Conversion Type", type text}, {"Start Phaseout", type date}, {"End Phaseout", type date}, {"Last PO Fcst/Order Date", type date}, {"Last PO Fcst/Order Date at FSD", type text}, {"FP Loc Run out Date", type text}, {"Current Inventory (BuOM)", Int64.Type}, {"Current Inventory (MSU)", type number}, {"Blocked Inventory (BuOM)", type any}, {"Blocked Inventory (MSU)", type any}, {"Additional FP inventory (BuoM)", Int64.Type}, {"Additional FP inventory (MSU)", Int64.Type}, {"POSB help", type any}, {"Planned Shipments+POSB demand (BuOM)", Int64.Type}, {"Planned Shipments+POSB demand (MSU)", Int64.Type}, {"Optimized Production Quantity (BuOM)", Int64.Type}, {"Optimized Production Quantity (MSU)", type number}, {"Production Capacity Gap", type any}, {"# MPQs to be Produced", type number}, {"Total Demand Satisfied (BuOM)", type number}, {"Total Demand Satisfied (MSU)", type number}, {"Total Demand Unsatisfied (BuOM)", type number}, {"Total Demand Unsatisfied (MSU)", type number}, {"Final Inventory (BuOM)", type number}, {"Final Inventory (MSU)", type number}, {"Final Inventory Scrap Cost ($)", type number}, {"Final Inventory Scrap Cost (Local Currency)", type number}, {"Final Inventory Scrap Cost (USD) at conversion", type number}, {"Final POSB remnants (BUoM)", type any}, {"Final POSB remnants (MSU)", type any}, {"Min Production Qty", Int64.Type}, {"Production Rounding Value", Int64.Type}, {"RPI/NPI Number", type any}})
in
    #"Changed Type"

SAP'den indirilen text dosyası indirecekseniz (csv):

Kod:

let
    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
    FileName = Excel.CurrentWorkbook(){[Name="FileName2"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(FilePath & FileName),[Delimiter="#(tab)", Columns=22, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type time}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", Int64.Type}}),
    #"Parsed Date" = Table.TransformColumns(#"Changed Type",{{"Column16", each Date.From(DateTimeZone.From(_)), type date}, {"Column17", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Parsed Date",{{"Column2", Int64.Type}, {"Column7", type text}, {"Column16", type text}, {"Column17", type text}})
in
    #"Changed Type1"

@emsey , Hocam senin için de kodlar aşağıdaki gibidir:

Kod:

Sub All()
'
' SAP Part

ActiveWorkbook.Save

    Set SapGuiAuto = GetObject("SAPGUI")
    Set App = SapGuiAuto.GetScriptingEngine
    Set Connection = App.Children(0)
    Set session = Connection.Children(0)
   
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nZC78"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/txtV-LOW").Text = [AB2]
session.findById("wnd[1]/usr/txtV-LOW").caretPosition = 7
session.findById("wnd[1]").sendVKey 0
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[1]/menu[2]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = [AB11]
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "stocks.txt"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 6
session.findById("wnd[1]/tbar[0]/btn[11]").press

' Remove filters

    Sheets("X1_FP_Report").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData
    Sheets("X2_Material_report").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData
    Sheets("SAP_MRP_elements").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData
    Sheets("TransformedSAPdata").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData

' Refresh Query

    Sheets("X1_FP_Report").Select
    Range("A2").Select
    ActiveWorkbook.Connections("Query - FP Report").Refresh
    Sheets("X2_Material_report").Select
    Range("A2").Select
    ActiveWorkbook.Connections("Query - Material Report").Refresh
    Sheets("SAP_MRP_elements").Select
    Range("A2").Select
    ActiveWorkbook.Connections("Query - stocks").Refresh

' Data Transformation on source tab

    Columns("L:L").Select
        Selection.Replace What:="ShipNt", Replacement:="SchAgr", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

' Creating new tab for raw data

    Columns("A:Z").Select
    Selection.Copy
    Sheets("TransformedSAPdata").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:X").EntireColumn.AutoFit
       
' Data transformation on TransformedSAPdata tab
       
    Columns("P:Q").Select
   
    Range("P:Q").NumberFormat = "dd-mm-yyyy"

    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
   
    Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Columns("Q:Q").Select
    Selection.TextToColumns Destination:=Range("Q1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

    Range("P1").Select
    ActiveWorkbook.Worksheets("TransformedSAPdata").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("TransformedSAPdata").AutoFilter.Sort.SortFields. _
        Add2 Key:=Range("stocks[[#All],[Column16]]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TransformedSAPdata").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Range("X1").Select
    Selection.Copy
    Columns("R:R").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.SpecialCells(xlCellTypeConstants, 2).Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
   
    Range("P1").Select
    ActiveWorkbook.Worksheets("TransformedSAPdata").AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("TransformedSAPdata").AutoFilter.Sort.SortFields. _
        Add2 Key:=Range("stocks[[#All],[Column16]]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("TransformedSAPdata").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

' Project name copying

Saygılar

Tefo · 7 Mart 2023

Hocam çok teşekkür ederim. Elleriniz dert görmesin bütün işimi çözdü.

emsey · 7 Mart 2023

smallburak' Alıntı:
Hocam dosya şirkete özel bilgiler içerdiği için paylaşmam doğru olmaz ama buradan yardımcı olmaya çalışacağım. Makro birkaç parçadan oluşuyor. Önce SAP'den stok dosyasını ZC78 kullanarak indiriyor. (Onu da query olarak ana dosyaya ekledim). İhtiyacım olan başka excel kaynak dosyalarımı da ana dosyaya query olarak ekledim. Makro, bu query'lere tek tek gidiyor ve refresh tuşuna basıyor. Böylece ana dosya tamamen güncelleniyor. Kodlara ihtiyacınız varsa lütfen belirtin. Şu anda iş bilgisayarım yanımda olmadığı için ulaşamıyorum. Yarın uygun bir zaman bulup, istediğiniz bilgileri buradan yazabilirim.

üstad advancen hücre adını ve yolunu sonra deneyeceğim. Yukarıda Şöyle demişsin "Makro, bu query'lere tek tek gidiyor ve refresh tuşuna basıyor. Böylece ana dosya tamamen güncelleniyor. Kodlara ihtiyacınız varsa lütfen belirtin. " işte tamda buna ihtiyacım var. Bende LOGO'dan aldığım sorgularım var. bunlar query ile bağlandığım ana bir excel dosyam var ancak sürekli içine girip dosya yenilemek istemiyorum. kendi kendi yenilesin istiyorum. En azından bir kere kendi yenilesin istiyorum. Bu kodlara ihtiyacım var

smallburak · 7 Mart 2023

Tefo' Alıntı:
Hocam çok teşekkür ederim. Elleriniz dert görmesin bütün işimi çözdü.

Güle güle kullan hocam

iyi günler

smallburak · 7 Mart 2023

emsey' Alıntı:
üstad advancen hücre adını ve yolunu sonra deneyeceğim. Yukarıda Şöyle demişsin "Makro, bu query'lere tek tek gidiyor ve refresh tuşuna basıyor. Böylece ana dosya tamamen güncelleniyor. Kodlara ihtiyacınız varsa lütfen belirtin. " işte tamda buna ihtiyacım var. Bende LOGO'dan aldığım sorgularım var. bunlar query ile bağlandığım ana bir excel dosyam var ancak sürekli içine girip dosya yenilemek istemiyorum. kendi kendi yenilesin istiyorum. En azından bir kere kendi yenilesin istiyorum. Bu kodlara ihtiyacım var

Hocam o kısım hemen aşağıda. Aslında remove filters istememişsin ama ben onu da ekledim. Mesela eklediğin query'de birşey araştırmak için filtreleme yaptın ve sonra da filtreyi kaldırmayı unuttun. Bu genel bir durum mu bilmiyorum ama benim dosyamda filtreleri kaldırmazsam, query güncellemesi olmuyor. Ben senin için onu da ekledim aşağıya.

Bu arada;
"X1_FP_Report" dediğim şey senin excel dosyandaki sayfa ismi.
"Query - FP Report" yazan kısım da kaynak dosyandaki sayfanın ismi. Yani kaynak dosyanda birkaç sheet varsa query doğru olanı bulmak ister. O yüzden böyle bir kod var. Diyelim ki senin sayfanın ismi Ahmet, sen bu kodu "Query - Ahmet" şeklinde değiştirmen gerekiyor.

Kod:

' Remove filters

    Sheets("X1_FP_Report").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData
    Sheets("X2_Material_report").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData
    Sheets("SAP_MRP_elements").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData
    Sheets("TransformedSAPdata").Select
    Range("A1").Select
    ActiveSheet.AutoFilter.ShowAllData

' Refresh Query

    Sheets("X1_FP_Report").Select
    Range("A2").Select
    ActiveWorkbook.Connections("Query - FP Report").Refresh
    Sheets("X2_Material_report").Select
    Range("A2").Select
    ActiveWorkbook.Connections("Query - Material Report").Refresh
    Sheets("SAP_MRP_elements").Select
    Range("A2").Select
    ActiveWorkbook.Connections("Query - stocks").Refresh

emsey · 7 Mart 2023

smallburak' Alıntı:
Hocam o kısım hemen aşağıda. Aslında remove filters istememişsin ama ben onu da ekledim. Mesela eklediğin query'de birşey araştırmak için filtreleme yaptın ve sonra da filtreyi kaldırmayı unuttun. Bu genel bir durum mu bilmiyorum ama benim dosyamda filtreleri kaldırmazsam, query güncellemesi olmuyor. Ben senin için onu da ekledim aşağıya.

Bu arada;
"X1_FP_Report" dediğim şey senin excel dosyandaki sayfa ismi.
"Query - FP Report" yazan kısım da kaynak dosyandaki sayfanın ismi. Yani kaynak dosyanda birkaç sheet varsa query doğru olanı bulmak ister. O yüzden böyle bir kod var. Diyelim ki senin sayfanın ismi Ahmet, sen bu kodu "Query - Ahmet" şeklinde değiştirmen gerekiyor.

Kod:

' Remove filters Sheets("X1_FP_Report").Select Range("A1").Select ActiveSheet.AutoFilter.ShowAllData Sheets("X2_Material_report").Select Range("A1").Select ActiveSheet.AutoFilter.ShowAllData Sheets("SAP_MRP_elements").Select Range("A1").Select ActiveSheet.AutoFilter.ShowAllData Sheets("TransformedSAPdata").Select Range("A1").Select ActiveSheet.AutoFilter.ShowAllData ' Refresh Query Sheets("X1_FP_Report").Select Range("A2").Select ActiveWorkbook.Connections("Query - FP Report").Refresh Sheets("X2_Material_report").Select Range("A2").Select ActiveWorkbook.Connections("Query - Material Report").Refresh Sheets("SAP_MRP_elements").Select Range("A2").Select ActiveWorkbook.Connections("Query - stocks").Refresh

Çok teşekkürler şirkette deneyeceğim emeğine sağlık