Accessからエクセルの列に挿入

質問

いつもお世話になっております。

AccessのVBAから開いているエクセル（代理店別進捗）の1行目の右側に2列を挿入をしたいのですが、下記のエラーが出てしまいました。
＊プログラムはとりあえず1列追加してみようと思って作ってます。

【エラー内容】
予期せぬエラーが発生しました。
エラーナンバー：424
エラー内容：オブジェクトが必要です。

'代理店別進捗の操作

Dim intColumn As Long

’1行目の左の列番号取得
intColumn = xlWB.Worksheets("代理店別進捗").Cells(Columns.Count).End(xlToLeft).select

'取得した列番号に列挿入
xlWB.Worksheets("代理店別進捗").Columns(intColumn).insert

回答No.7

kkkkkm

'書式を%に変更
が正常で
'代理店別進捗の操作
でエラーになるのでしたら
途中にあるのは

myRs.Close

だけなのでそれを後ろに移動してみるとか

'xlWB.sheets("代理店別進捗").select
を

xlWB.sheets("代理店別進捗").Activate

で有効にするとか

そのあたりを変更して自分で考えてください。

回答No.6

kkkkkm

あと
.Workbooks.Open(strTemplate)
が二回あります。他にも意味がなかったり位置が悪かったりするところがあると思いますので一度見直した方がいいと思います。

補足

in Columnの前のSetを抜いたのですが、
エラー424（オブジェクトが必要です）
と表示されてしまいます・・・

************************************************************
'代理店別進捗の操作

'xlWB.sheets("代理店別進捗").select

Dim intColumn

intColumn = xlWB.Worksheets("代理店別進捗").Cells(1, Columns.Count).End(xlToLeft).Column
xlWB.Worksheets("代理店別進捗").Columns(intColumn).insert

'***************************************************************

回答No.5

kkkkkm

Set intColumn = xlWB.Wo

ここにSetはいりません

回答No.4

kkkkkm

前回
Set xlWB = .Workbooks.Open(strTemplate)
(前後は略してます)
としてSetしていたと思いますが、それをしてますかという話です。

補足

はい、Set xlWB = .Workbooks.Open(strTemplate)と記述しています。

繰り返し処理するところは削除していますが、
プログラムは下記になります。
実行すると同じ424　オブジェクトが表示されます。

Set IntColumn＝にカーソルを合わせると
IntColumn＝Empty値と表示されます。

Private Sub コマンド144_Click()

Dim strsql1 As String
Dim strTemplate As String
Dim strFileName As String
Dim ExpFileName As String
Dim xlapp As Object
Dim xlWB As Object
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset

'ExportData削除
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE from T_EDI_01_CVJ"

DoCmd.SetWarnings True

'Export用クエリ実行
DoCmd.SetWarnings False
DoCmd.OpenQuery ("D_EDI_01_CVJ2")

DoCmd.SetWarnings True

'ﾌｧｲﾙ名作成
ExpFileName = "FY24_03_CVJ_EDI" & "_" & Format(Date, "yyyymmdd")
strFileName = GetFileName(False, "MicrosoftExcel ブック (*.xlsx)|*.xlsx", "", ExpFileName & ".xlsx")

'EXCELｱﾌﾟﾘｹｰｼｮﾝを起動
Set xlapp = CreateObject("Excel.Application")

'ｾｯﾄする過程が見えないよう一旦不可視
xlapp.Visible = False

Set myCn = CurrentProject.Connection

strsql1 = "T_EDI_01_CVJ"

'ﾚｺｰﾄﾞｾｯﾄｵｰﾌﾟﾝ
myRs.Open strsql1, myCn, adOpenForwardOnly, adLockReadOnly

With xlapp
'ﾃﾝﾌﾟﾚｰﾄを開く
strTemplate = Application.CurrentProject.Path & "\" & "FY24_03_xxx_CVJ_EDI.xlsx"
Set xlWB = .Workbooks.Open(strTemplate)

'ﾃﾝﾌﾟﾚｰﾄﾌｧｲﾙが存在しないときはｴﾗｰ
If Dir(strTemplate) = "" Then
MsgBox "テンプレートファイルを確認してください。", vbOKOnly + vbCritical, "エラー"
.Visible = True
.Quit
Exit Sub
End If

'ﾃﾝﾌﾟﾚｰﾄﾌｧｲﾙｵｰﾌﾟﾝ
.Workbooks.Open strTemplate

'******************************************************************

'T_EDI_01_CVJをCVJに出力する。
'T_EDI_01_CVJの結果値出力処理(1行目にﾍｯﾀﾞｰを表示しているので、2行目1列目からｾｯﾄ
xlWB.Worksheets("CVJ").Rows(2).insert
xlWB.Worksheets("CVJ").Rows(2).ClearFormats
xlWB.Worksheets("CVJ").Cells(2, 1).CopyFromRecordset myRs

Dim I As Long

I = 2

xlWB.Worksheets("CVJ").Activate
Do While xlWB.Worksheets("CVJ").Cells(I, 1) <> ""

I = I + 1

Loop

'書式を%に変更
xlWB.Worksheets("CVJ").Columns("C:C").numberFormatLocal = "0%"
xlWB.Worksheets("CVJ").Columns("A:J").Font.Name = "Meiryo UI"

myRs.Close

'*******************************************************************
'代理店別進捗の操作

'xlWB.sheets("代理店別進捗").select

Dim intColumn

Set intColumn = xlWB.Worksheets("代理店別進捗").Cells(1, Columns.Count).End(xlToLeft).Column
xlWB.Worksheets("代理店別進捗").Columns(intColumn).insert
'*******************************************************************

'完了したら保存
If Len(strFileName) = 0 Then
xlWB.Close SaveChanges:=False
xlapp.Quit
MsgBox "処理を中止します。", vbOKOnly + vbInformation

Exit Sub
Else
xlWB.SaveAs FileName:=strFileName

End If

MsgBox "TX Shuttle用ファイルの出力が完了しました。", vbOKOnly + vbInformation

End With

Set myRs = Nothing: Close
Set myCn = Nothing: Close

'Excelを終了します
xlapp.Quit
Exit Sub
End Sub

回答No.3

kkkkkm

> でデバックが出て、カーソルを合わせるとintColumn = 0となりました。

そこでエラーになったのでしたら、そのコードは正しく実行されていませんからintColumnには値が代入されていないと思います。

> 回答１でおっしゃっていた、１がないからということでしょうか？

1が無いと1を省略したことになり1行目になりますから、上記の0になっているのとは関係が無いと思います。

> どのように１を書いたらいいのでしょうか？

Cells(行, 列)
ですので
Cells(1, Columns.Count)
です。ただ、一行目はセル結合をしているみたいですので結果はGWの列番号が返ってきますから注意してください。

xlWBは前回のようにSetしているのでしょうか。

補足

Cells(行, 列)なのですね。ありがとうございます。
下記のようにSetを付けているのですが、
Set IntColumn＝の部分でデバックがでました。。。

実行時エラー　４２４
オブジェクトが必要です。

Dim intColumn As Long

Set intColumn = xlWB.Worksheets("代理店別進捗").Cells(1, Columns.Count).End(xlToLeft).Column
xlWB.Worksheets("代理店別進捗").Columns(intColumn).insert

回答No.2

kkkkkm

回答No.1の追加です。

参照設定でMicrosoft Excel XX.X Object Libraryのチェックはしてますよね。

補足

kkkkkmさん
回答ありがとうございます。

Microsoft Excel 16.0 object~にチェックは入っていました。

intColumn = xlWB.Worksheets("代理店別進捗").Cells(Columns.Count).End(xlToLeft).Column

でデバックが出て、カーソルを合わせるとintColumn = 0となりました。

回答１でおっしゃっていた、１がないからということでしょうか？どのように１を書いたらいいのでしょうか？

回答No.1

kkkkkm

xlWBがSetされているのでしたら
.End(xlToLeft).select
は
.End(xlToLeft).Column
じゃないでしょうか
本題とは関係ありませんが
Cells( Columns.Count)
1を省略しない方がいいような気もします。
もしOn Error GoToを使っているのでしたら正常に動くまでは利用しないようにしてた方がいいと思います。