マクロまたは他の方法でで自動転記

質問

現在マクロ・VBAについて独学で勉強しています。
そこで、ご存じの方いらっしゃいましたら、ご教授お願いします。

下記図1の納入先に入力したものを、自動で図2の項目に転記させたいです。

=［1］sheet1!A1
上記で、転記も考えたのですが、発注書のファイルを毎回見積番号で管理している為
いちいちファイル名を手動で変えなければならない手間があり、
他に良い方法があれば教えて頂きたいです。

回答No.6

kkkkkm

> （INDIRECT関数はそのファイルを開いていないとエラー表示になります）、

結果が確定したらファイルを閉じる前にコピーして同じ場所に値貼り付けしたらいいだけの話ですよね。言わずもがなの話だと思いましたが…。

私が同じことをするならINDIRECTは使わずに迷わすマクロにしますが。とりあえず他の方法の一つということで。

マクロなら単純に図２の最終行に図１のデータを追加していけばいいだけだと思いますが。セルの位置関係が違いそうなので単純なコピペだと無理そうだから、必要なセルの部分だけ、図2に代入すればいいだけの話で、印刷なんてマクロの記録で記録したものを流用すればいいだけの話ですし、しいていえば同じ見積もり番号のものがあればどうするか、印刷はするけど一覧には載せない、もしくは上書きするとか、の問い合わせする機能が必要なくらいだと思いますが。図2に見積り番号持たせてFindで探せば面倒じゃないでしょう。

とりあえず、そんなの面倒な問題でもありませんから質問者さんは頑張ってください。

回答No.5

mitarashi

inputForm.xlsmのSheet1が入力フォームとします。同ブックのSheet2の3行目に数式を入れて、転記したいデータが一行に並ぶようにします。その範囲（ここではA3:E3)にtenkiという名前をつけます。Sheet2のA1にはデータ転記先のブックのパスを入れておきます。ここではdatabase.xlsxとします。
database.xlsxのSheet1の一行目に列見出しを入れておきます。（F1～F5が見出しのつもりです）今回はあまり関係無いですが、今後役に立つでしょう。
データ入力後、testを実行すると、database.xlsxにデータが転記されます。database.xlsxは開いておく必要はありません。（開いていても転記される様です）
ADOでのワークブックへの追記がやってみたかっただけの、興味本位のコードです。ご参考まで。
Sub test()
Dim blResult As Boolean
With ThisWorkbook.Sheets("Sheet2")
blResult = addRecord(.Cells(1).Value, "Sheet1", .Range("tenki"))
End With
End Sub

Function addRecord(wbkpath As String, destSheetName As String, newDataRange As Range) As Boolean
Dim cn As Object, rs As Object
Dim mySQL As String
Dim i As Long, j As Long

Const adOpenStatic = 3
Const adLockOptimistic = 3

On Error GoTo errHandle
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
With cn
.Provider = "Microsoft.ace.OLEDB.12.0"
.ConnectionString = "Data Source=" & wbkpath & ";" & _
"Extended Properties='Excel 12.0; HDR=Yes'"
.Open
End With
mySQL = "select * from [" & destSheetName & "$]"
rs.Open mySQL, cn, adOpenStatic, adLockOptimistic
With rs
For i = 1 To newDataRange.Rows.Count
.addnew
For j = 1 To newDataRange.Columns.Count
.Fields(j - 1).Value = newDataRange.Cells(i, j).Value
Next j
.Update
Next i
End With
errHandle:
If Err.Number = 0 Then
addRecord = True
Else
Debug.Print Err.Number & Err.Description
End If
If Not rs Is Nothing Then
If rs.State = 1 Then rs.Close
Set rs = Nothing
End If
If Not cn Is Nothing Then
If rs.State = 1 Then rs.Close
Set cn = Nothing
End If
End Function

回答No.4

MackyNo1

>発注用テンプレは毎回同じですばやく印刷ができれば良いです。現状が毎回図1のテンプレを開いては入力→名前を付けて保存→図2に手入力しています。そうではなく発注書を作成した時点で、自動で何日に何が何個出荷されました。のような、流れを取りたいです。

このパターンなら以下のページを参考にして、テンプレートデータから必要な項目をデータベースシートに追加するマクロコードを作成すればよいと思います。

http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/prog/prog01.html

紹介したURLのコードは最下行の１つ下のセルにセル範囲をコピーするコードになっていますが、特定のセルのコピーにして、テンプレートシートのそれぞれのデータを貼り付けるようなコードにすれば対応できると思います。
そのままのコードを使いたいなら、どこかのセルに転記したいセルを、１行のデータになるようにリンク貼り付けして（同じシートに作成するなら、このリンクセルの行は非表示にしておけばよい）、このセルを値貼り付けでデータベースシートに転記するようにすることもできます。

もちろん、データベースシートに転記前あるいは転記後に、テンプレートファイルを印刷するコードを挿入し、最後にテンプレートファイルのデータを削除する操作で終わるコードにしてください。

なお、このようなデータ追加操作は関数では対応できませんので（INDIRECT関数はそのファイルを開いていないとエラー表示になります）、マクロを利用することになります。

回答No.3

kkkkkm

補足です

="[Book" & ROW(A1001) &".xlsx]Sheet1!A1"

この式の ROW(A1001)の数値部分は、ファイルの最小値の番号部分に合わせおくと、あとは下にコピーするだけで

たとえば、式の中のBookの部分だけ取り上げて記載すると

Book100１.xlsx
Book1002xlsx
Book1003xlsx

のように勝手に変化していきますので、セルの指定は変更しなくても大丈夫です。

マクロの場合、

Workbooks.Open Filename:="転記先ファイル名"
Workbooks("転記先ファイル名").Worksheets("Sheet1"). Range("A" & Range("A" & Rows.Count).End(xlUp).Row+1 ).Value = Range("A1").Value

見たいなものを、2行目以降必要な分列挙するくらいしか、助言できません。

回答No.2

kkkkkm

図１の単票のデータを図２の一覧データ(一枚の単票のデータが横方向に並んでいる)に転記したいという事だと考え
単票データのアイル名が

Book100１.xlsx
Book1002xlsx
Book1003xlsx

のよう連続しているものと仮定して

Book1001.xlsxを開いて必要なデータを入力します。

以下のセルの指定は適当ですので実際のデータを反映したいセル番号に変更してください。

まず、一覧データのファイル(仮にBookXとします)のSheet2のA1に
="[Book" & ROW(A1001) &".xlsx]Sheet1!A1"
と記入します。セルには[Book1001.xlsx]Sheet1!A1と表示されます。

BookXのShhet1のA1に
=IFERROR(INDIRECT(Shhet2!A1),"")
と記入します。エラー処理はご自身のエクセルのバージョンに合わせて適宜変更してください。

そうすると、Book1001.xlsxのデータが表示されます。

うまくいったら、BookXのSheet2のA1の式を必要なだけ下と右にコピー(セルの指定は適宜調整してください)し、Sheet1の式もも同じようにコピーします。

これでファイル名が連続している場合に限りますが、式は都度変更しなくても随時データが反映されていきます。

回答No.1

MackyNo1

>=［1］sheet1!A1
上記で、転記も考えたのですが、発注書のファイルを毎回見積番号で管理している為いちいちファイル名を手動で変えなければならない手間があり、他に良い方法があれば教えて頂きたいです。

質問内容が把握できないのですが、上記の操作でファイル名を自動的に書き換えれば、うまくいくということでしょうか？

全体的なイメージからすると、発注書に記載されているデータを順次データベースに追加したいように思うのですが、もしこのケースなら、たとえば「データ追跡機能付きテンプレートウィザード」を利用するのが簡単です。
これをマクロで実行するなら、テンプレートの発注書ファイルにマクロを仕込んでおき、マクロでデータベースファイルを立ち上げ、データベースシートの最下行の下の行にデータを追加するような仕様にします。

私なら、発注書シートとデータベースを同じブックにまとめ、発注用シートは印刷用だけに使用し（残しておく必要はないのでは）、それらのデータをマクロでデータベースシートの最下行に追加していくマクロコードを書く方法で対処すると思います（ファイルを立ち上げる操作がないので、素早くマクロ処理できるので勧めです）。

補足

ご回答ありがとうございます！こちらから質問させてください。発注書・データベースを同ブックについて詳しくお聞かせ願えませんか？発注用テンプレは毎回同じですばやく印刷ができれば良いです。現状が毎回図1のテンプレを開いては入力→名前を付けて保存→図2に手入力しています。そうではなく発注書を作成した時点で、自動で何日に何が何個出荷されました。のような、流れを取りたいです。ユーザーフォーム作成になりますか？