hiko-blog’s diary

VBA業務改善

MENU

アクセスDataからエクセルへ展開したい。

アクセスDataからエクセルへ展開したい。

今度は逆にアクセルDataから簡単にVBA利用して、エクセルへ展開する方法ないのかな。。。

同じようにデータベース接続を利用すれば、エクセルへの展開できますよ。

やっぱりできるんですね♪そこのとこ詳しくお願いします。

ハイ、では、前回同様、概略手順を下記にサンプルとして記しますね♪

 

概略手順
  1. データベース接続オブジェクトの宣言
  2. レコードセットオブジェクト
  3. アクセスのテーブル名
  4. アクセステーブルの項目名 ※アクセス項目名=エクセル項目名であること
  5. 取り込みたいDataの式追加。
サンプル
Sub Accessからimport()
  Dim mycon As New ADODB.Connection    'データベース接続オブジェクト
  Dim mrs As New ADODB.Recordset  'レコードセットオブジェクト
 
  Dim mySQL As String  'Accessの対象オブジェクト
  Dim dbPath As Variant  'データベースファイルオブジェクト
  Dim msn As Variant ’アクティブシート名
  Dim i As Integer
  
  dbPath = "D:\ProgramData\Access\FileDB.accdb"   'データベースファイルのありか
  mycon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbPath & ""   
  mycon.Open
  
  mySQL = "SELECT * FROM アクセスのテーブル名" 
  
 
  mrs.Open mySQL, mycon, adOpenDynamic
  
  msn = ActiveSheet.Name
 
’以下 サンプル式なので、検索条件に合わせてVBA作成必要。
------ここからーーーーーーーーーーーーーー
  i = 2  'Data貼り付け行の位置
  
  Do Until mrs.EOF
    With Worksheets(msn)
      .Cells(i, 1).Value = mrs!acn1   ’アクセステーブルの項目名
      .Cells(i, 2).Value = mrs!acn2
      .Cells(i, 3).Value = mrs!acn3
      .Cells(i, 4).Value = mrs!acn4
    End With
      i = i + 1
      mrs.MoveNext
  Loop
------ここまでーーーーーーーーーーーーーー
  mrs.Close
  Set mrs = Nothing
  mycon.Close
  Set mycon = Nothing
End Sub

 

ADOを使うための準備設定です。

VBE画面-メニューの「ツール」-「参照設定」で、

Microsoft ActiveX Data Objects **** Library を設定しておいてね♪

エクセルDataをアクセスDataへ取り込みたい。

エクセルDataをアクセスDataへ取り込みたい。

エクセルDataを簡単にVBA利用して、アクセスで取り込む方法ないのかな。。。

データベース接続を利用すれば、外部取り込みData取り込みでできますよ。

ほんとですか♪いつものように教えてください。

ハイ、では、概略手順を下記にサンプルとして記しますね♪

 

概略手順
  1. データベース接続オブジェクトの宣言
  2. レコードセットオブジェクト
  3. アクセスのテーブル名
  4. アクセステーブルの項目名 ※アクセス項目名=エクセル項目名であること
  5. 取り込みたいDataの式追加。
サンプル
Sub Accessへのexport()
  Dim msn As Worksheet
  Set msn = ThisWorkbook.Worksheets("export") '取り込みData場所は、仮に export としておく
   
  Dim mycon As New ADODB.Connection    'データベース接続オブジェクト
  Dim dbPath As String
  dbPath = "D:\ProgramData\Access\FileDB.accdb"
  mycon.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & dbPath & ";"
  mycon.Open
  
  Dim mrs  As New ADODB.Recordset 'レコードセットオブジェクト
  mrs.Open "アクセスのテーブル名", mycon, adOpenDynamic, adLockPessimistic
 
 ’以下 サンプル式なので、検索条件に合わせてVBA作成必要。
 ------ここからーーーーーーーーーーーーーー
Dim acn1 As String ’アクセステーブルの項目名   補足:アクセス項目名=エクセル項目名であること
  Dim acn3 As String
  Dim acn4 As String
  Dim i As String
  
i = 2
 Do Until msn.Cells(i, 1) = ""
  acn1 = msn.Cells(i, 1)
  acn2 = msn.Cells(i, 2)
  acn3 = msn.Cells(i, 3)
  acn4 = msn.Cells(i, 4)
 
  mrs.AddNew
  mrs!acn1 = acn1
  mrs!acn2 = acn2
  mrs!acn3 = acn3
  mrs!acn4 = acn4
  mrs.Update
    i = i + 1
 Loop
------ここまでーーーーーーーーーーーーーー
 
  mrs.Close
  mycon.Close
  
  Set msn = Nothing
  Set mycon = Nothing
  Set mrs = Nothing
  
  MsgBox "Dataを登録しました", vbInformation, "登録完了"
    
End Sub

 

Access エクセルからインポート取り込み時のAcSpreadSheetの値メモ。

AcSpreadSheetType

Access エクセルからインポート取り込み時、vba設定 AcSpreadSheet値のメモ。

忘れないうちにメモメモ。。。

 

AcSpreadSheetType 列挙(Access)

名前 説明
acSpreadsheetTypeExcel3 0 Microsoft Excel 3.0 形式
acSpreadsheetTypeExcel4 6 Microsoft Excel 4.0 形式
acSpreadsheetTypeExcel5 5 Microsoft Excel 5.0 形式
acSpreadsheetTypeExcel7 5 Microsoft Excel 95 形式
acSpreadsheetTypeExcel8 8 Microsoft Excel 97 形式
acSpreadsheetTypeExcel9 8 Microsoft Excel 2000 形式
acSpreadsheetTypeExcel12 9 Microsoft Excel 2010 形式
acSpreadsheetTypeExcel12Xml 10 Microsoft Excel 2010/2013/2016 XML 形式 (.xlsx、.xlsm、.xlsb)

このブックには更新できないリンクが…を解除したい。

このブックには更新できないリンクが…を解除したい。

このブックには更新できないリンクが 1つ以上含まれていますってなんの…毎回警告が出て嫌なんですけど…

リンク先が無くなってエクセルさんが探しても見つかりませんって言ってるんですよ。

前から共有されて使用している資料なんですが、僕は何も悪いことしてないはずなんですが。。。

原因は別ファイルからの移動やコピー時のリンク先欠如でエクセルあるあるですね♪

んっ…?ひょっとしたら自分が犯人なのかな…どうやって直せば良いのか教えてください。

手直し段取りは下記に記しますね♪

 

対象調査(エラー個所の特定)※主な5つ
  1. 外部リンク(他ブック)のエラー(数式、セル、オブジェクト)
  2. 名前定義のリンク先エラー
  3. 条件付き書式
  4. 入力規則
  5. リンクの解除
方法

1.外部リンク(他ブック)のエラー(数式、セル、オブジェクト)

 数式:Ctrl + F で「検索と置換」を呼び出し、検索する文字列に「[」(角かっこ)を入力し、不要そうなリンク先がないかチェック。

 

セル、オブジェクト:Ctrl + G で「ジャンプ」を呼び出し、セル選択に入り、

コメント、定数、オブジェクトを選択し怪しいリンク先がないかチェック。

 

 2.名前定義のリンク先エラー ※下記vba利用も可。

 Ctrl + F3で「名前の管理」を呼び出し、定義している名前にエラーがないかチェック。

hiko-blog.hatenadiary.com

 3.条件付き書式

[ホーム]タブの[条件付き書式]で不要な条件式を見つけ出し、条件式リセット。

 

 4.入力規則等々

[データ]タブの[データの入力規則]からリストにエラーリンク先がないかチェック。

5.リンクの解除
[データ]タブの[接続]の[リンクの編集]から「リンクの解除」。

 
1~5でもリンク先不明な場合
[データ]タブの[接続]の[リンクの編集]下にある[起動時の確認]から、[メッセージを表示しないで、リンクの自動更新も行わない]を選択し強制的に更新させない。
 
その他
 

1~5を行っても、迷宮入りの場合、素直にあきらめるか資料作り替えた方が早いです。

急がば回れ…?ちょっと違うか…

全てのシートの保護解除を一気にしたい。

全てのシートの保護解除を一気にしたい。

シートの保護解除の繰り返しって、意外にストレスです。。。

VBA使えば一気に保護解除、保護設定も容易になりますよ♪

それはありがたい、是非とも教えてください。

コードは、下記に記しますね♪

 

シート保護
Sub シート保護()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="0843" ’0843は、仮パスワードです、任意のパスに♪
Next
End Sub
 
 シート保護解除
Sub シート保護解除()
Dim ws As Worksheet
For Each W In Worksheets
ws.Unprotect Password:="0843" ’0843は、仮パスワードです、任意のパスに♪
Next
End Sub
 
 おまけ

sheetではなくbookの場合は、下記の通りです♪♪

勉強なります、メモメモ。。。

Sub ブック保護()
 ActiveWorkbook.Protect Password:="0843"
Next

End Sub
 
 
Sub ブック保護解除()
 ActiveWorkbook.Unprotect Password:="0843"
End Sub
 
 

特定のシートを見せたくないんです。

  •  特定のシートを見せたくないんです
  • イメージ
  • 非表示コード
  •  表示コード
  • おまけ
 特定のシートを見せたくないんです

特定のシートを見せたくない時ってありませんか?

ついつい、再表示してみたくなります。

VBA上だと、エクセルシートから再表示できなくできるんです(見えなくするんです)

マジですか、是非とも知りたいです。

では、下記にコード記しますね♪

 

イメージ

f:id:hiko-blog:20200217233018p:plain

 ↓ マクロ実行後、

f:id:hiko-blog:20200217233051p:plain

右クリック上からも、再表示選択できなくなります。

非表示コード
Sub 特定のシート表示させない()
Dim sheetname
sheetname = "隠したいシート"
Worksheets(sheetname).Visible = xlSheetVeryHidden
End Sub
 
 表示コード
 Sub 特定のシート表示させる()
Dim sheetname
sheetname = "隠したいシート"
Worksheets(sheetname).Visible = xlSheetVisible
End Sub
 
続きを読む

選択したFileを開きたい。

目次

作成目的

VBA作成時、任意でファイル選択できるようにする為。

コード

Sub 選択File開く()
Dim 選択File As String
選択File = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
If 選択File <> "False" Then
Workbooks.Open 選択File
Else
MsgBox "キャンセル"
End If
End Sub

バックアップのバッチファイル作成。

サンプル例として、

Rem コピー基 C:\Users\/”ここはユーザー名を記載”\Desktop\20200213
Rem コピー先(バックアップ先 D:\MYDOCUMENT\Mydocument\BackUp\ の場合。

 

Backup.bat

<コード>

 

XCOPY "C:\Users\”ここはユーザー名を記載”\Desktop\20200213" "D:\MYDOCUMENT\Mydocument\BackUp\" /d /e /h /r /y

 

 

今日の日付フォルダーを作りたい。

何やかんや当日フォルダーを作成し、業務作業Dataをまとめておく。

※整理整頓、証跡残しの為。。。

 

今日の日フォルダー作成.bat

<コード>

Rem 今日の日フォルダー作成
@echo off

set Dir_Name=%DATE:/=%
mkdir C:\Users\”ここはユーザー名です”\Desktop\%Dir_Name%
explorer %Dir_Name%

 

 バッチ処理前。

f:id:hiko-blog:20200209203741p:plain

  ↓ バッチ処理後。

f:id:hiko-blog:20200209203815p:plain

 

フォルダー内の大量にあるPDFファイルをプリントアウトしたい。

ほんと地味な作業が多いので、自動化させます。

 

PDF_printout.bat

<コード>

 

@echo off
start "" "AcroRd32.exe"
pause
for %%i in (*.pdf) do (
echo 印刷中 %%i
START ACRORD32.EXE /n /t "%%i"
timeout 1 &gt; nul

 

 

保存したいフォルダに日付追加

日々のData収取結果をBOOK単位で保存の為。

 ※xlsm⇒xlsxで保存(データのみ保存していきたいのです)

 

<サンプル>マクロ実行前

f:id:hiko-blog:20200207200247p:plain

    ↓マクロ実行後

f:id:hiko-blog:20200207200454p:plain

<コード>パスなし

 

Sub 保存したいフォルダに日付追加() 'パスなし

'Filename:= 保存場所です
'FileFormat:= File形式です

 

ActiveWorkbook.SaveAs _
Filename:="C:\Users\karku\Desktop\さんぷる\保存したいFile名" & "_" & Format(Date, "yyyymmdd"), _
FileFormat:=xlOpenXMLWorkbook
End Sub

 

 

 <コード>パスワード保存したい時



Sub 保存したいフォルダに日付け追加2() 'パス付

'Filename:= 保存場所です
'FileFormat:= File形式です
'Password:= パスワード
'WriteResPassword:= パスワード

 

ActiveWorkbook.SaveAs _
Filename:="C:\Users\karku\Desktop\さんぷる\さんぷるfile_" & Format(Date, "yyyymmdd"), _
FileFormat:=xlOpenXMLWorkbook, _
Password:="0843", _
WriteResPassword:="0843", _
ReadOnlyRecommended:=True
End Sub

 

 

任意のフォルダー一気に作成したい。

  •  無駄にランダムにある資料、、、共有ファイル整備の為、必要に迫られてバッチファイル対応を行う。

<段取り>

前提: list.txt と フォルダー作成.bat は同じフォルダー内であること。

1.テキストファイルを作成し、テキスト内に作りたいフォルダー名を記載。

今回は下記コード通り、list.txtとします。

 

2.下記コードのバッチファイル作成。

 

3.フォルダー作成.bat をダブルクリックすれば、

list.txt内に記載した名前のフォルダーが作成されます。

 

 

フォルダー作成.bat

 <コード>

 

 @echo off
pushd %~dp0
for /f %%i in (list.txt) do mkdir %%i
exit

 

 

<イメージ>

list.text

f:id:hiko-blog:20200206203927p:plain

↓フォルダー作成.bat ダブルクリック実施後、フォルダー作成します。

f:id:hiko-blog:20200206204416p:plain

 

ExcelVBA関連の質問掲示板

==========ExcelVBA関連の質問掲示板==============================================

ちょっとした悩みつまずき参考になります。

 

エクセルの学校

Excelの質問掲示板です。

http://www.excel.studio-kazu.jp/

 

teratail(テラテイル)

プログラミングに特化したQ&Aサイトです。

https://teratail.com/

 

Excelの質問掲示板です。

https://excelfactory.net/excelboard/excelvba/excel.cgi

 

モーグ

Excelの質問掲示板です。
https://www.moug.net/