Excel����͉�ʂɂ���Access�f�[�^�x�[�X���Q�ƁE�X�V�E�폜����ۂ֗̕��e�N�j�b�N�FVBA�^�}�N���֗�Tips�i1/3 �y�[�W�j

�Ɩ��������ɖ𗧂�VBA�^�}�N���̂��܂��܂�Tips���R�[�h��������ďЉ�Ă����܂��B����́ARecordset��Open���\�b�h��SQL�����g�����@��AMoveNext�AFind�ADelete���\�b�h�AEOF�v���p�e�B�̎g�����ARecordset�̊e�t�B�[���h�̎Q�ƕ��@�Ȃǂɂ‚��āB

» 2015�N12��10�� 05��00�� ���J
[��t�������CPROJECT KySS]
uVBA^}N֗TipsṽCfbNX

�A�ږڎ�

���{Tips�̊‹��FWindows 8.1 Enterprise�i64�r�b�g�j�{Excel 2013�{Access 2013


�@����́A�uExcel����Access�̃f�[�^���X�V����v���@�ƁuExcel����Access�̃f�[�^���폜������@�v�𒆐S�ɂ��܂��܂ȘA�g�e�N�j�b�N���Љ��B

�@�g�p����e�[�u���́ATIPS�uAccess����Excel�ɐڑ����ĊJ�����@��Access�ō쐬�����f�[�^��Excel�ɏo�͂�����@�Ŋw�ԁA�A�gVBA�̊�{�v�ō쐬���Ă������A�u���i�Ǘ��e�[�u���v���B�u���i�Ǘ��f�[�^�x�[�X.accdb�v���N���b�N���āAAccess���N�����Ă����Ăق����B

Excel����Access�̃f�[�^���X�V����

�@Access�́u�ڋq�Ǘ��e�[�u���v�̃f�[�^��Excel�V�[�g�ɕ\�������A�V�[�g��Ŋe���ڂ̒l��ύX���āA�u�ҏW�ۑ��v�{�^�����N���b�N����ƁA�ύX���ꂽ���e��Access�́u�ڋq�Ǘ��e�[�u���v�ɔ��f����鏈�����������B

Excel�̓��̓Z���ƃ{�^�������

�@�܂��́A�u���i�Ǘ��f�[�^�x�[�X.xlsm�v���N���b�N���āAExcel���N������B�V�[�g�̉��ɕ\������Ă���u���Ɂ{�v�̃A�C�R�����N���b�N���āA�uSheet1�v��lj����A���O���uAccess�f�[�^�̍X�V�v�Ƃ��Ă����i�}1�j�B

�}1�@�uSheet1�v��lj����A���O�ɁuAccess�Ƀf�[�^�̍X�V�v�Ƃ��Ă���

�@�}1�́uAccess�f�[�^�̍X�V�v�̃V�[�g�ɁAAccess�́u���i�Ǘ��e�[�u���v�̃f�[�^��ǂݍ��ރZ���ƁA�uAccess�̏��i�Ǘ��f�[�^��ǂݍ��ށv�{�^���ƁA�u�ҏW�ۑ��v�{�^�����쐬���Ă����B�܂��A���ږ��͎��O�ɏ�����ݒ肵�č쐬���Ă����i�}2�j�B

�@�{�^����Excel���j���[�́u�}���v���u�}�`�v����u�p�ێl�p�`�v��I�����č쐬���Ă���B

�}2�@�uAccess�f�[�^�̍X�V�v�̃V�[�g�ɍ��ږ��ƁA�{�^����2�”z�u����

Access�́u���i�Ǘ��e�[�u���v�̃f�[�^��ǂݍ���VBA�R�[�h

�@Excel���j���[�́u�J���v���uVisual Basic�v�ƑI�����āA�uVBE�v�iVisual Basic Editor�j���N������B�v���W�F�N�g����Module1�ɁA���X�g1�̃R�[�h���L�q����B

Sub Access�̏��i�Ǘ��f�[�^��ǂݍ���()
  Dim myCon As New ADODB.Connection
  Dim myRecordSet As New ADODB.Recordset
  Dim mySQL As String
  Dim dbFile As Variant
  Dim mySheetName As Variant
  Dim i As Integer
  
  dbFile = "K:\Access_Excel VBA Tips\���i�Ǘ��f�[�^�x�[�X.accdb"
  myCon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
  myCon.Open
  
  mySQL = "SELECT * FROM ���i�Ǘ��e�[�u��"
  
  myRecordSet.Open mySQL, myCon, adOpenDynamic
  
  mySheetName = ActiveSheet.Name
  
  i = 2
  
  Do Until myRecordSet.EOF
    With Worksheets(mySheetName)
      .Cells(i, 1).Value = myRecordSet!���iNO
      .Cells(i, 2).Value = myRecordSet!���i��
      .Cells(i, 3).Value = myRecordSet!�P��
      .Cells(i, 4).Value = myRecordSet!�݌�
    End With
      i = i + 1
      myRecordSet.MoveNext
  Loop
  myRecordSet.Close
  Set myRecordSet = Nothing
  myCon.Close
  Set myCon = Nothing
End Sub
���X�g1�@Access�́u���i�Ǘ��e�[�u���v�̃f�[�^��ǂݍ��ރR�[�h

�@12�s�ڂ܂ł̃R�[�h��Tips�uExcel����OLE DB�v���o�C�_�[���g����Access�ɐڑ������{�v�Ɠ��l�Ȃ̂ŁA�ȗ�����B�ȉ��A�R�[�h�ɂ‚��ẮA�قȂ镔���̂݉������B

�@�Ȃ�����́A�uData Source�v�ɕϐ��udbFile�v�Ɋi�[����Ă���l���w�肵�Ă���i9�`10�s�ځj�B

ADODB.Recordset.Open��SELECT����SQL���w��

�@������^�̕ϐ��umySQL�v�ɁA�u���i�Ǘ��e�[�u���v�̑S�Ẵ��R�[�h���擾����SQL�����w�肷��i13�s�ځj�B

�@ADODB.Recordset.Open���\�b�h�ŁA�f�[�^�\�[�X���̃e�[�u������f�[�^�̓ǂݏ������s�����������邪�ATips�uExcel����OLE DB�v���o�C�_�[���g����Access�ɐڑ������{�v�ł�ADODB.Recordset.Open���\�b�h�̐����ƈقȂ�A�uSource�v�ɂ�SELECT����SQL���w�肵�Ă���i15�s�ځj�B

�@���̂悤�ɁA�uSource�v�ɂ̓e�[�u�����ł�SQL���ł��A�ǂ���ł��w��ł���B

ADODB.Recordset.Open���\�b�h�̏���

ADODB.Recordset.Open Source:="�oSQL���p", ActiveConnection:=�o�f�[�^�\�[�X�ւ̐ڑ����������Ă���R�l�N�V�����I�u�W�F�N�g�p, CursorType:=�o�e�[�u����N�G�����J���Ƃ��̃J�[�\���̎�ށp, LockType:=�o�r������̕��@�p


���R�[�h�̍Ō��\���uADODB.Recordset.EOF�v�v���p�e�B

�@�ϐ��ui�v���u2�v�ŏ���������i19�s�ځj�B�ϐ�i�͍s�ԍ���\���̂ŁAAccess�f�[�^�̓ǂݍ��݂��u2�s�ځv����s���Ӗ��Łu2�v�ŏ��������Ă���B

�@�u���i�Ǘ��e�[�u���v�̃��R�[�h�̍Ō�imyRecordSet.EOF�v���p�e�B�j�܂ŁA22�`29�s�ڂ̏������J��Ԃ��B

ADODB.Recordset�́u!�v�{�t�B�[���h���Ńt�B�[���h���w��ł���

�@�A�N�e�B�u�ȃV�[�g�̕ϐ�i�s�ڂ́u1�v��ڂɁu���iNO�v�t�B�[���h�̒l���w�肷��i23�s�ځj�B

�@ADODB.Recordset�̌�́u!�v�i���Q���j�́A�u���iNO�v�t�B�[���h��SQL���Ŏw�肵��ADODB.Recordset�̍\���v�f�ł��邱�Ƃ�\���Ă���B

�@���l�ɁA2��ڂɂ́u���i���v�A3��ڂɂ́u�P���v�A4��ڂɂ́u�݌Ɂv�Ƃ��ꂼ��̃t�B�[���h�̒l���w�肷��i24�`26�s�ځj�B

���̃��R�[�h�Ɉړ�����uADODB.Recordset.MoveNext�v���\�b�h

�@�ϐ�i�̒l��1���‘��������i28�s�ځj�AmyRecordSet.MoveNext���\�b�h�ŁA���̃��R�[�h�Ɉړ�����i29�s�ځj�B

�@����ŁA�u���i�Ǘ��e�[�u���v�ɓo�^����Ă���f�[�^���A����Excel�̃Z���ɕ\�������悤�ɂȂ�B

Access�f�[�^�̓ǂݍ��݂����s

�@���s����ƁA�}3�̂悤�ɂȂ�B

�}3�@Excel�́uAccess�f�[�^�̍X�V�v�V�[�g�Ɂu���i�Ǘ��e�[�u���v�̃f�[�^���ǂݍ��܂ꂽ
�@�@�@�@�@�@ 1|2|3 ���̃y�[�W��

Copyright © ITmedia, Inc. All Rights Reserved.

'; this.insertTarget = document.querySelector('#cmsBody .subscription') || document.querySelector('#cmsBody .inner'); }; BodyAdIMSWithCCE.prototype = Object.create(BodyAdContent.prototype); BodyAdIMSWithCCE.prototype.activate = function () { refreshGam('InArtSpecialLink'); } // global reference window.itm = itm; //entry point BodyAdEventBase.polyfill(); const bodyAdManager = BodyAdManager.getInstance(); bodyAdManager.addEventListener(BodyAdManager.EVENTS.READY, function (ev) { bodyAdManager.loadAdvertise(); }); bodyAdManager.init(); })();
�X�|���T�[����̂��m�点PR

���ڂ̃e�[�}

Microsoft  WindowsőO2025
AI for GWjAO
[R[h^m[R[h Zg by IT - ITGWjArWlX̒SŊ􂷂gD
Cloud Native Central by IT - XP[uȔ\͂gD
�V�X�e���J���m�E�n�E �y�����i�r�zPR
���Ȃ��ɂ������߂̋L��PR

RSS�ɂ‚���

�A�C�e�B���f�B�AID�ɂ‚���

���[���}�K�W���o�^

��IT�̃��[���}�K�W���́A �������A���ׂĖ����ł��B���Ѓ��[���}�K�W�������w�ǂ��������B