麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 資料抓取另存在不同sheet, 出現錯誤1004 [打印本頁]

作者: b7502040 時間: 2014-1-13 14:08 標題: 資料抓取另存在不同sheet, 出現錯誤1004

各位前輩好
我是新手，在練習時碰到以下問題

CommandButton1設定在sheet1
想讓資料存取放在sheet2

所以我的步驟如下
1. click CommandButton1
2. 選取Sheet2，並清除舊有資料
3. 進入外部連結匯入資料，存放在Sheet2的A1儲存格

但執行時卻顯示
執行階段錯誤'1004':
應用程式或物件定義上的錯誤

請問以下程式應該如何修改？
多謝指教

Private Sub CommandButton1_Click()

Sheets("Sheet2").Select
Columns("A:AC").Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;http://mops.twse.com.tw/mops/web/ajax_t51sb01?step=1&firstin=1&TYPEK=sii" _
      , Destination:=Range("$A$1"))
      .Name = "ajax_t51sb01?step=1&firstin=1&TYPEK=sii"
      .FieldNames = True
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .WebSelectionType = xlEntirePage
      .WebFormatting = xlWebFormattingNone
      .WebPreFormattedTextToColumns = True
      .WebConsecutiveDelimitersAsOne = True
      .WebSingleBlockTextImport = False
      .WebDisableDateRecognition = False
      .WebDisableRedirections = False
      .Refresh BackgroundQuery:=False
End With

Columns("C:AB").Select
Selection.Delete Shift:=xlToLeft

Rows("1:1").Select
Selection.Delete Shift:=xlUp

End Sub

作者: Hsieh 時間: 2014-1-13 15:03

回復 1# b7502040

Private Sub CommandButton1_Click()
With Sheets(2)
.Columns("A:AC").ClearContents
With .QueryTables.Add(Connection:= _
"URL;http://mops.twse.com.tw/mops/web/ajax_t51sb01?step=1&firstin=1&TYPEK=sii" _
, Destination:=.Range("$A$1"))
.Name = "ajax_t51sb01?step=1&firstin=1&TYPEK=sii"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
.Columns("C:AB").Delete Shift:=xlToLeft
.Rows("1:1").Delete Shift:=xlUp
End With
End Sub

複製代碼

作者: b7502040 時間: 2014-1-13 23:11

多謝板主回覆!!

作者: GBKEE 時間: 2014-1-14 07:35

回復 3# b7502040

Private Sub CommandButton1_Click() '工作表物件模組(Sheet1) 的程式碼
Sheets("Sheet2").Select '作用中的工作表轉移到Sheet2
Columns("A:AC").Select '1004錯誤:這工作表物件模組(Sheet1):指定一個儲存格或一個儲存格範圍
'*********************************************
'當作用中的工作表(Sheet2)不是工作表物件模組(Sheet1)的工作表
'不能指定這工作表物件模組(Sheet1)工作表的一個儲存格或一個儲存格範圍
'這程式碼如在一般模組,ThisWorkbook,不會有這錯誤.
'*****************************************************
Selection.ClearContents 'Selection是Sheet2的沒有1004
Range("A1").Select '同上有1004錯誤

複製代碼

作者: bulletin 時間: 2014-1-20 12:01

請問前輩

依照這樣的作法，修改了另外一個檔案
程式碼可以執行，但是全部都貼在sheet1
而非指定的CFQ, INC, ISQ, BSQ當中
這是程式碼哪個地方沒指定清楚呢？

Private Sub CommandButton1_Click()

With Sheets("CFSQ")
      With QueryTables.Add(Connection:= _
         "URL;http://dj.mybank.com.tw/z/zc/zc3/zc3_2002.djhtm", Destination:=Range( _
         "$A$1"))
         .Name = "zc3_2002.djhtm"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .WebSelectionType = xlSpecifiedTables
         .WebFormatting = xlWebFormattingNone
         .WebTables = "3"
         .WebPreFormattedTextToColumns = True
         .WebConsecutiveDelimitersAsOne = True
         .WebSingleBlockTextImport = False
         .WebDisableDateRecognition = False
         .WebDisableRedirections = False
         .Refresh BackgroundQuery:=False
      End With
End With

With Sheets("ISQ")
      With QueryTables.Add(Connection:= _
         "URL;http://dj.mybank.com.tw/z/zc/zcq/zcq_2002.asp.htm", Destination:=Range( _
         "$A$1"))
         .Name = "zcq_2002.asp"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .WebSelectionType = xlSpecifiedTables
         .WebFormatting = xlWebFormattingNone
         .WebTables = "3"
         .WebPreFormattedTextToColumns = True
         .WebConsecutiveDelimitersAsOne = True
         .WebSingleBlockTextImport = False
         .WebDisableDateRecognition = False
         .WebDisableRedirections = False
         .Refresh BackgroundQuery:=False
      End With
End With

With Sheets("BSQ")
      With QueryTables.Add(Connection:= _
         "URL;http://dj.mybank.com.tw/z/zc/zcp/zcpa/zcpa_2002.asp.htm", Destination:= _
         Range("$A$1"))
         .Name = "zcpa_2002.asp"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .WebSelectionType = xlSpecifiedTables
         .WebFormatting = xlWebFormattingNone
         .WebTables = """oMainTable"""
         .WebPreFormattedTextToColumns = True
         .WebConsecutiveDelimitersAsOne = True
         .WebSingleBlockTextImport = False
         .WebDisableDateRecognition = False
         .WebDisableRedirections = False
         .Refresh BackgroundQuery:=False
      End With
End With

With Sheets("INC")
      With QueryTables.Add(Connection:= _
         "URL;http://dj.mybank.com.tw/z/zc/zch/zch_2002.asp.htm", Destination:=Range( _
         "$A$1"))
         .Name = "zch_2002.asp"
         .FieldNames = True
         .RowNumbers = False
         .FillAdjacentFormulas = False
         .PreserveFormatting = True
         .RefreshOnFileOpen = False
         .BackgroundQuery = True
         .RefreshStyle = xlInsertDeleteCells
         .SavePassword = False
         .SaveData = True
         .AdjustColumnWidth = True
         .RefreshPeriod = 0
         .WebSelectionType = xlSpecifiedTables
         .WebFormatting = xlWebFormattingNone
         .WebTables = """oMainTable"""
         .WebPreFormattedTextToColumns = True
         .WebConsecutiveDelimitersAsOne = True
         .WebSingleBlockTextImport = False
         .WebDisableDateRecognition = False
         .WebDisableRedirections = False
         .Refresh BackgroundQuery:=False
      End With
End With

End Sub

作者: GBKEE 時間: 2014-1-20 12:36

本帖最後由 GBKEE 於 2014-1-21 09:35 編輯

回復 5# bulletin
這問題太多人問過

With Sheets("CFSQ")
      With QueryTables.Add(Connection:= _
         "URL;http://dj.mybank.com.tw/z/zc/zc3/zc3_2002.djhtm", Destination:=.Range( _
         "$A$1"))

With 中少了一點. 就不是 With 的屬性或方法

作者: bulletin 時間: 2014-1-20 12:57

不好意思
我知道了
爬過文，眼睛太大顆沒發現 "."的存在
>"<

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)