run-time error: Expecting object to be local

tamer42 · 9 Kasım 2023

Merhaba aşağıdaki kodda;
Kaynak dosya' dan veri çekmek isterken
Application.CommandBars("Queries and Connections").Visible = False

bu satırda hata veriyor

yardımlarınız için şimdiden teşekkürler,
iyi Çalışmalar.

Kod:

Sub ImportAndConvertCsv()
Dim qr
Dim WS As Worksheet
 Dim LastRow As Long
Dim CsvLocation As String

'Clean
''On Error GoTo EH

''On Error Resume Next


Set WS = ActiveSheet

If WS.Range("A1") <> "" Then
    WS.Range("A1:G1").Select
    Selection.AutoFilter
    WS.Range("A1").Select
End If

For Each qr In ThisWorkbook.Queries
    qr.Delete
Next qr

FileDialogButtonClick



CsvLocation = Range("CsvLocation").Value

Range("CsvLocation") = vbNullString '" & CsvLocation & "
    ActiveWorkbook.Queries.Add Name:="CsvQuery", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""" & CsvLocation & """),[Delimiter="","", Columns=7, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Pr" & _
        "omoted Headers"",{{""Market - Store Name"", type text}, {""Order - Number"", Int64.Type}, {""Date - Order Date"", type text}, {""Item - Qty"", Int64.Type}, {""Item - Options"", type text}, {""Ship To - Country"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([#""Item - Options""] <> """"))," & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter"" = Ta" & _
        "ble.SplitColumn(#""Filtered Rows"", ""Item - Options"", Splitter.SplitTextByDelimiter("","", QuoteStyle.Csv), {""Item - Options.1"", ""Item - Options.2"", ""Item - Options.3""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type1"" = Table.TransformColumnTypes(#""Split Column by Delimiter"",{{""Item - Options.1"", type text}, {""Item - Options.2"", type text}, {""Item - Options.3"", type text}}" & _
        ")," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Changed Type1"",{""Item - Options.3""})," & Chr(13) & "" & Chr(10) & "    #""Merged Columns"" = Table.CombineColumns(#""Removed Columns"",{""Item - Options.1"", ""Item - Options.2""},Combiner.CombineTextByDelimiter("" "", QuoteStyle.None),""Merged"")," & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter1"" = Table.SplitColumn(#""Merged Columns"", ""Date - Ord" & _
        "er Date"", Splitter.SplitTextByEachDelimiter({"" ""}, QuoteStyle.Csv, false), {""Date - Order Date.1"", ""Date - Order Date.2""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type2"" = Table.TransformColumnTypes(#""Split Column by Delimiter1"",{{""Date - Order Date.2"", type time}})," & Chr(13) & "" & Chr(10) & "    #""Changed Type3"" = Table.TransformColumnTypes(#""Changed Type2"",{{""Date - Order Date.2"", type number}" & _
        "})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter2"" = Table.SplitColumn(#""Changed Type3"", ""Date - Order Date.1"", Splitter.SplitTextByDelimiter(""/"", QuoteStyle.Csv), {""Date - Order Date.1.1"", ""Date - Order Date.1.2"", ""Date - Order Date.1.3""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type4"" = Table.TransformColumnTypes(#""Split Column by Delimiter2"",{{""Date - Order Date.1.1"", Int64.Type" & _
        "}, {""Date - Order Date.1.2"", Int64.Type}, {""Date - Order Date.1.3"", Int64.Type}})," & Chr(13) & "" & Chr(10) & "    #""Merged Columns1"" = Table.CombineColumns(Table.TransformColumnTypes(#""Changed Type4"", {{""Date - Order Date.1.2"", type text}, {""Date - Order Date.1.1"", type text}, {""Date - Order Date.1.3"", type text}}, ""tr-TR""),{""Date - Order Date.1.2"", ""Date - Order Date.1.1" & _
        """, ""Date - Order Date.1.3""},Combiner.CombineTextByDelimiter(""."", QuoteStyle.None),""Date"")," & Chr(13) & "" & Chr(10) & "    #""Changed Type5"" = Table.TransformColumnTypes(#""Merged Columns1"",{{""Date"", type date}})," & Chr(13) & "" & Chr(10) & "    #""Changed Type6"" = Table.TransformColumnTypes(#""Changed Type5"",{{""Date"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Added Custom"" = Table.AddColumn(#""Changed Type6"", ""Date - " & _
        "Order Date"", each [Date]+[#""Date - Order Date.2""])," & Chr(13) & "" & Chr(10) & "    #""Removed Columns1"" = Table.RemoveColumns(#""Added Custom"",{""Date"", ""Date - Order Date.2""})," & Chr(13) & "" & Chr(10) & "    #""Reordered Columns"" = Table.ReorderColumns(#""Removed Columns1"",{""Market - Store Name"", ""Order - Number"", ""Date - Order Date"", ""Item - Qty"", ""Merged"", ""Amount - Shipping Cost"", ""Ship To " & _
        "- Country""})," & Chr(13) & "" & Chr(10) & "    #""Changed Type7"" = Table.TransformColumnTypes(#""Reordered Columns"",{{""Date - Order Date"", type datetime}})," & Chr(13) & "" & Chr(10) & "    #""Sorted Rows"" = Table.Sort(#""Changed Type7"",{{""Date - Order Date"", Order.Ascending}})," & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"" = Table.RenameColumns(#""Sorted Rows"",{{""Merged"", ""Item - Options""}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Renamed Columns"""
    
   
    LastRow = WS.Range("K1").Value + 1
    If LastRow > 1 Then LastRow = LastRow + 1
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""CsvQuery"";Extended Properties=""""" _
        , Destination:=Range("$A$" & LastRow)).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [CsvQuery]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "CsvQuery"
        .Refresh BackgroundQuery:=False
    End With
    
    Application.CommandBars("Queries and Connections").Visible = False
    WS.Range("A" & LastRow & ":G" & LastRow).Select

    WS.ListObjects("CsvQuery").Unlist
    
    Columns("B:B").ColumnWidth = 11.29
    Columns("D:D").ColumnWidth = 3.86
    Columns("F:F").ColumnWidth = 6
    Columns("G:G").ColumnWidth = 3.57
    Columns("E:E").ColumnWidth = 61.71
    
    ActiveWorkbook.Queries(1).Delete
    If LastRow > 1 Then
        Range(LastRow & ":" & LastRow).Delete
    End If
    
    WS.Columns("F:F").Select
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
With WS
    .Cells.Interior.ColorIndex = xlNone
    .Cells.Borders.LineStyle = xlNone
    .Cells.Font.ColorIndex = xlAutomatic
    
    .Range("H1").Value = " "
    .Range("J1").Value = "OrderQty"
    .Range("L1").Value = "ItemQty"
    .Range("K1").Formula = "=SUBTOTAL(3,B:B)-1"
    .Range("M1").Formula = "=SUBTOTAL(9,D:D)"
    .Range("K:K").ColumnWidth = 5
    .Range("M:M").ColumnWidth = 5
    .Range("1:1").RowHeight = 30
    .Range("1:1").VerticalAlignment = xlCenter
    .Range("K1,M1").HorizontalAlignment = xlCenter
    .Range("K1,M1").VerticalAlignment = xlCenter
   ' .Range("K1,M1").Style = "Calculation"
    '.Range("J1,L1").Style = "Input"
End With
  
    'LastFormatting
    WS.Columns("F:F").Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    LastRow = WS.Range("K1").Value + 1
    WS.Range("H2").Select
    WS.Range("H2").Formula = "=IF(B2<>B1,NUMBERVALUE(F2),0)"
    WS.Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H" & LastRow)
    WS.Range("F2:F" & LastRow).Value = Range("H2:H" & LastRow).Value
    WS.Range("H2:H" & LastRow).Clear


    WS.Range("H2").Select
    WS.Range("H2").Formula = "=IF(B2<>B1,NUMBERVALUE(F2),0)"
    WS.Range("H2").Select
    Selection.AutoFill Destination:=WS.Range("H2:H" & LastRow)
    WS.Range("F2:F" & LastRow).Value = WS.Range("H2:H" & LastRow).Value
    WS.Range("H2:H" & LastRow).Clear
    
        'Stop
    
    WS.Range("A1:G1").Select
    Selection.AutoFilter
    WS.Columns("C:C").Select
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
        Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    'Stop
    Selection.NumberFormat = "m/d/yyyy h:mm:ss"

    WS.Range("A2").Select
    ActiveWindow.FreezePanes = True
    WS.Range("A1").Select
    
    Set WS = Nothing
    
    Exit Sub
    
EH:
    MsgBox "Error# " & Err.Number & ": " & Err.Description
    

End Sub

Korhan Ayhan · 9 Kasım 2023

O satırı neden kullanıyorsunuz?

tamer42 · 9 Kasım 2023

Korhan Ayhan' Alıntı:
O satırı neden kullanıyorsunuz?

Korhan Hocam bir arkadaş yazmış bu kodları da benden bazı düzenlemeler rica etmişti, kodu sıfırdan yazmayayım diye üzerinden gidiyordum
Application.CommandBars("Queries and Connections").Visible = False
burası önemli bir şey değilmiş; bu satırı silince bir sıkıntı olmadı

Yalnız verileri çektikten sonra şimdi şöyle bir sıkıntı var, ekli görselde görüleceği üzere; "F" sütunu (Amount - Shipping Cost) "#Değer" olarak gösteriyor,

Kaynak dosyada yer alan sayısal değerden kaynaklı olabilir mi?

Market - Store Name,Order - Number,Date - Order Date,Item - Qty,Item - Options,Amount - Shipping Cost,Ship To - Country
PositiveCustomArt,"3065969662","10/28/2023 9:50:55 PM","1","BELLA CANVAS SIZES: Unisex T-shirt - L, COLORS: Navy","4.13","US"

teşekkürler,
iyi akşamlar.

Korhan Ayhan · 9 Kasım 2023

Bende başka hatalar verdiği için kodu tam deneyemedim..

Forumda CSV dosyasından veri almak için onlarca örnek paylaşıldı. Alternatif olarak onları deneyebilirsiniz.

tamer42 · 9 Kasım 2023

Korhan Ayhan' Alıntı:
Bende başka hatalar verdiği için kodu tam deneyemedim..

Forumda CSV dosyasından veri almak için onlarca örnek paylaşıldı. Alternatif olarak onları deneyebilirsiniz.

tamamdır Korhan Hocam
teşekkürler,

run-time error: Expecting object to be local

tamer42

Destek Ekibi

Ekli dosyalar

Korhan Ayhan

Administrator

tamer42

Destek Ekibi

Ekli dosyalar

Korhan Ayhan

Administrator

tamer42

Destek Ekibi