Kết nối vào dữ liệu phần mềm MISA SME 2017

Liên hệ QC
quangnguyentbb

quangnguyentbb

Thành viên mới
Tham gia
17/10/18
Bài viết
21
Được thích
5
Em chào các anh/chị.
Anh/chị cho em hỏi là khi em chạy file vba để lấy dữ liệu từ sql thì phần mềm báo lỗi là "Run time error -2147217865 (80040e37) Automation error" ạ.
Em chưa hiểu tìm lỗi ở đâu vì phần mềm không báo phải debug ở chỗ nào ạ. Em có gửi cả link file ở bên dưới ạ
File gốc :https://drive.google.com/open?id=1mdHZQ1rwg27kiXNfEXoRZmaWXToNWSaT
Mục tiêu của em là lấy dữ liệu từ sql từ phần mềm MISA 2017 ạ
Cảm ơn anh/chị!
Mã: 
Sub Button1_Click()
    DataExtract
End Sub
Function DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

m_Server = Worksheets("DATA").Range("B1").Value
m_UserName = Worksheets("DATA").Range("B2").Value
m_Password = Worksheets("DATA").Range("B3").Value
m_Database = Worksheets("DATA").Range("B4").Value


' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
'strConn = "Provider=SQLOLEDB.1;Data Source=(LOCAL);Initial Catalog=DAICAC;Integrated Security=SSPI"
If (m_UserName = "" And m_Password = "") Then
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + m_Database + ";Integrated Security=SSPI"
Else
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + _
        m_Database + ";User ID=" + m_UserName + ";Password=" + m_Password
End If

'strConn = "Provider=SQLOLEDB.1;Data Source=" + Worksheets("DATA").Range("B1") + ";Initial Catalog=" + Worksheets("DATA").Range("B4") + ";User ID=" + Worksheets("DATA").Range("B2") + ";Password=" + Worksheets("DATA").Range("B3") + ""
          
'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
  
    ' Lay so lieu ra sheet DMDT
    .Open "SELECT dbo.AccountingObject.AccountingObjectCode, dbo.AccountingObject.AccountingObjectName, space(1) as Ten_tieng_anh, dbo.AccountingObject.Address, space(1) as Dia_chi_tieng_anh, dbo.AccountingObject.CompanyTaxCode, space(1) as Doi_tac, space(1) as Doi_tac_tieng_anh, dbo.AccountingObject.Website, dbo.AccountingObject.EmailAddress, dbo.AccountingObject.Tel, dbo.AccountingObject.Fax, dbo.AccountingObject.BankAccount,space(1) as Chu_tk, dbo.AccountingObject.BankName , dbo.AccountingObject.AccountingObjectCategory, 0 AS Loai_Dt FROM dbo.AccountingObject"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMDT").Range("A2").CopyFromRecordset rsPubs
    .Close
    
    ' Lay so lieu ra sheet DMNH
    .Open "SELECT dbo.Bank.BankCode, dbo.Bank.BankName, dbo.Bank.BankNameEngLish,  dbo.Bank.Address, space(1) as Dia_chi_tieng_anh, space(1) as Mst, space(1) as Doi_tac, space(1) as Doi_tac_Ta, space(1) as Website, space(1) as Email, space(1) as Dien_thoai, space(1) as Fax, space(1) as Tk_Nh, space(1) as Chu_Tk, space(1) as Ten_Nh, space(1) as Nhom_Dt, 0 as Loai_Kh FROM dbo.Bank"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMNH").Range("A2").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet DMHD
    .Open "SELECT dbo.Contract.ContractCode, dbo.Contract.ContractName, space(1) as Ten_hop_dong_tieng_anh, dbo.RefType.RefTypeName, dbo.Contract.AccountingObjectBankAccount, dbo.AccountingObject.AccountingObjectCode,  dbo.Contract.SignedDate, dbo.Contract.AmountOC, dbo.Contract.Amount FROM dbo.Contract INNER JOIN   dbo.RefType ON dbo.Contract.RefType = dbo.RefType.RefType INNER JOIN dbo.AccountingObject ON dbo.Contract.AccountingObjectID = dbo.AccountingObject.AccountingObjectID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMHD").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet TONKHOKT
    .Open "SELECT dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode,dbo.InventoryItem.Unit, 1 as He_so_quy_doi, dbo.OpeningInventoryEntry.Quantity, dbo.OpeningInventoryEntry.Quantity, dbo.OpeningInventoryEntry.UnitPrice, dbo.OpeningInventoryEntry.Amount,dbo.OpeningInventoryEntry.PostedDate FROM dbo.OpeningInventoryEntry INNER JOIN dbo.InventoryItem ON dbo.OpeningInventoryEntry.InventoryItemID = dbo.InventoryItem.InventoryItemID INNER JOIN dbo.Stock ON dbo.OpeningInventoryEntry.StockID = dbo.Stock.StockID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("TONKHOKT").Range("A2").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet PNMH
    .Open "SELECT dbo.PUInvoice.PURefDate, dbo.PUInvoice.RefNo, dbo.PUInvoiceDetail.InvDate, dbo.PUInvoiceDetail.InvNo, dbo.PUInvoiceDetail.InvSeries, space(1) as BP, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.PUInvoice.PUJournalMemo, dbo.PUInvoiceDetail.CreditAccount, dbo.Stock.StockCode,  dbo.InventoryItem.InventoryItemCode, dbo.PUInvoiceDetail.Unit, dbo.PUInvoice.CurrencyID, dbo.PUInvoice.ExchangeRate, dbo.PUInvoiceDetail.Quantity, dbo.PUInvoiceDetail.UnitPriceOC, dbo.PUInvoiceDetail.UnitPrice," & _
        "dbo.PUInvoiceDetail.AmountOC, dbo.PUInvoiceDetail.Amount, dbo.PUInvoiceDetail.VATRate, dbo.PUInvoiceDetail.VATAmountOC, dbo.PUInvoiceDetail.VATAmount, dbo.PUInvoiceDetail.ImportTaxAmount FROM dbo.PUInvoice INNER JOIN  dbo.PUInvoiceDetail ON dbo.PUInvoice.RefID = dbo.PUInvoiceDetail.RefID  INNER JOIN  dbo.Contract ON dbo.PUInvoiceDetail.ContractID = dbo.Contract.ContractID INNER JOIN  dbo.AccountingObject ON dbo.PUInvoice.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo" & _
        ".Stock ON dbo.PUInvoiceDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.PUInvoiceDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID "
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PNMH").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet HDBH
    .Open "SELECT dbo.SAInvoice.InvDate, dbo.SAInvoice.InvNo, dbo.SAInvoice.InvSeries, space(1) as BP, space(1) as HD, dbo.AccountingObject.AccountingObjectCode, dbo.SAInvoice.SAJournalMemo, dbo.SAInvoiceDetail.DebitAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.SAInvoiceDetail.Unit, dbo.SAInvoice.CurrencyID, dbo.SAInvoice.ExchangeRate, dbo.SAInvoiceDetail.Quantity, dbo.SAInvoiceDetail.UnitPriceOC, dbo.SAInvoiceDetail.UnitPrice, dbo.SAInvoiceDetail.AmountOC, dbo.SAInvoiceDetail.Amount, dbo.SAInvoiceDetail.VATRate," & _
        "dbo.SAInvoiceDetail.VATAmountOC, dbo.SAInvoiceDetail.VATAmount,space(1) as Gia_von,space(1) as Tien_von,0 as Dinh_khoan FROM dbo.SAInvoice INNER JOIN dbo.AccountingObject ON dbo.SAInvoice.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.SAInvoiceDetail ON dbo.SAInvoice.RefID = dbo.SAInvoiceDetail.RefID INNER JOIN dbo.Stock ON dbo.SAInvoiceDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.SAInvoiceDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID AND dbo.SAInvoiceDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("HDBH").Range("A3").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet HBTL
    .Open "SELECT dbo.SAReturn.RefDate, dbo.SAReturn.RefNo, dbo.SAReturn.InvDate, dbo.SAReturn.InvNo, dbo.SAReturn.InvSeries, space(1) as BP, space(1) as HD, dbo.AccountingObject.AccountingObjectCode, dbo.SAReturn.JournalMemo, dbo.SAReturnDetail.CreditAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.SAReturnDetail.Unit, dbo.SAReturn.CurrencyID, dbo.SAReturn.ExchangeRate, dbo.SAReturnDetail.Quantity, dbo.SAReturnDetail.UnitPriceOC, dbo.SAReturnDetail.UnitPrice, dbo.SAReturnDetail.AmountOC," & _
        "dbo.SAReturnDetail.Amount, dbo.SAReturnDetail.VATRate, dbo.SAReturnDetail.VATAmountOC, dbo.SAReturnDetail.VATAmount FROM dbo.SAReturnDetail INNER JOIN dbo.SAReturn ON dbo.SAReturn.RefID = dbo.SAReturnDetail.RefID INNER JOIN dbo.AccountingObject ON dbo.SAReturn.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.SAReturnDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.SAReturnDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID where dbo.SAReturn.RefType='354'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("HBTL").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet DMVT
    .Open "SELECT dbo.InventoryItem.InventoryItemCode, dbo.InventoryItem.InventoryItemName, space(1) as Ten_tieng_anh, dbo.InventoryItem.Unit, space(1) as Dvt_tieng_anh, dbo.InventoryItem.UnitPrice, dbo.InventoryItem.SalePrice, dbo.InventoryItem.ConvertUnit, dbo.InventoryItem.ConvertRate, space(1) as Gia_mua_1, space(1) as Gia_ban_1, space(1) as Dv_quy_doi_2, space(1) as He_so_quy_doi_2, space(1) as Gia_mua_2, space(1) as Gia_ban_2, space(1) as Dv_quy_doi_3, space(1) as He_so_quy_doi_3, space(1) as Gia_mua_3, space(1) as Gia_ban_3," & _
        "space(1) as Dv_quy_doi_4, space(1) as He_so_quy_doi_4, space(1) as Gia_mua_4, space(1) as Gia_ban_4, space(1) as Loai_Vt,  dbo.InventoryItemCategory.InventoryCategoryCode, dbo.InventoryItem.MinimumStock, space(1) as Sl_ton_toi_da, dbo.InventoryItem.InventoryAccount, dbo.InventoryItem.COGSAccount, dbo.InventoryItem.SaleAccount,space(1) as Tk_Ck, space(1) as Tk_HBTL, space(1) as Ma_Sp, space(1) as Nhom_Dm_Sp, dbo.InventoryItem.TaxRate FROM dbo.InventoryItem INNER JOIN dbo.InventoryItemCategory ON dbo.InventoryItem.InventoryCategoryID = dbo.InventoryItemCategory.InventoryCategoryID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMVT").Range("A2").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet SDDKTK
    .Open "SELECT dbo.OpeningAccountEntry.PostedDate, space(1) as So_chung_tu,space(1) as Ma_bo_phan,dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, space(1) as Dien_giai_tieng_viet,space(1) as Dien_giai_tieng_anh,dbo.OpeningAccountEntry.CurrencyID, dbo.OpeningAccountEntry.ExchangeRate, dbo.OpeningAccountEntry.AccountNumber, dbo.OpeningAccountEntry.DebitAmount, dbo.OpeningAccountEntry.CreditAmount,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.DebitAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_no_ngoai_te,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.CreditAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_co_ngoai_te,space(1) as Han_thanh_toan, dbo.OpeningAccountEntry.DebitAmount," & _
        "dbo.OpeningAccountEntry.CreditAmount,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.DebitAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_no_ngoai_te,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.CreditAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_co_ngoai_te FROM dbo.OpeningAccountEntry LEFT OUTER JOIN dbo.AccountingObject ON dbo.OpeningAccountEntry.AccountingObjectID = dbo.AccountingObject.AccountingObjectID LEFT OUTER JOIN dbo.Contract ON dbo.OpeningAccountEntry.ContractID = dbo.Contract.ContractID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("SDDKTK").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet PNTP
    .Open "SELECT dbo.INInwardOutward.PostedDate, dbo.INInwardOutward.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INInwardOutward.JournalMemo, dbo.INInwardOutwardDetail.DebitAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INInwardOutwardDetail.Unit, dbo.INInwardOutwardDetail.Quantity FROM dbo.INInwardOutward LEFT OUTER JOIN dbo.INInwardOutwardDetail ON dbo.INInwardOutward.RefID = dbo.INInwardOutwardDetail.RefID LEFT OUTER JOIN dbo.Contract ON dbo.INInwardOutwardDetail.ContractID = dbo.Contract.ContractID LEFT OUTER JOIN dbo.AccountingObject ON dbo.AccountingObject.AccountingObjectID = dbo.INInwardOutward.AccountingObjectID LEFT OUTER JOIN dbo.Stock ON dbo.INInwardOutwardDetail.StockID = dbo.Stock.StockID LEFT OUTER JOIN dbo.InventoryItem ON dbo.INInwardOutwardDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID WHERE dbo.INInwardOutward.RefType = '201'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PNTP").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet PXLR
    .Open "SELECT dbo.INAssemblyDisassembly.INPostedDate, dbo.INAssemblyDisassembly.INRefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INAssemblyDisassembly.INJournalMemo, dbo.INAssemblyDisassemblyDetail.DebitAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INAssemblyDisassembly.Quantity, Stock1.StockCode, InventoryItem1.InventoryItemCode, dbo.INAssemblyDisassemblyDetail.Unit, dbo.INAssemblyDisassemblyDetail.Quantity, dbo.INAssemblyDisassemblyDetail.UnitPrice," & _
        "dbo.INAssemblyDisassemblyDetail.Amount FROM dbo.INAssemblyDisassembly INNER JOIN dbo.INAssemblyDisassemblyDetail ON dbo.INAssemblyDisassembly.RefID = dbo.INAssemblyDisassemblyDetail.RefID LEFT OUTER JOIN dbo.Contract ON dbo.INAssemblyDisassemblyDetail.ContractID = dbo.Contract.ContractID LEFT OUTER JOIN dbo.AccountingObject ON dbo.INAssemblyDisassembly.INAccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.INAssemblyDisassembly.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.INAssemblyDisassembly.InventoryItemID = dbo.InventoryItem.InventoryItemID INNER JOIN dbo.Stock Stock1 ON dbo.INAssemblyDisassemblyDetail.StockID = Stock1.StockID INNER JOIN dbo.InventoryItem InventoryItem1 ON dbo.INAssemblyDisassemblyDetail.InventoryItemID = InventoryItem1.InventoryItemID WHERE dbo.INAssemblyDisassembly.RefType = '211'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PXLR").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet PNLR
    .Open "SELECT dbo.INAssemblyDisassembly.INRefDate, dbo.INAssemblyDisassembly.INRefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INAssemblyDisassembly.INJournalMemo, dbo.INAssemblyDisassembly.CreditAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INAssemblyDisassemblyDetail.Quantity, Stock1.StockCode, InventoryItem1.InventoryItemCode, InventoryItem1.Unit, dbo.INAssemblyDisassembly.Quantity, dbo.INAssemblyDisassembly.UnitPrice," & _
        "dbo.INAssemblyDisassembly.Amount FROM dbo.INAssemblyDisassembly INNER JOIN dbo.INAssemblyDisassemblyDetail ON dbo.INAssemblyDisassembly.RefID = dbo.INAssemblyDisassemblyDetail.RefID LEFT OUTER JOIN dbo.Contract ON dbo.INAssemblyDisassemblyDetail.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.INAssemblyDisassembly.INAccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.INAssemblyDisassemblyDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.INAssemblyDisassemblyDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID INNER JOIN dbo.Stock Stock1 ON dbo.INAssemblyDisassembly.StockID = Stock1.StockID INNER JOIN dbo.InventoryItem InventoryItem1 ON dbo.INAssemblyDisassembly.InventoryItemID = InventoryItem1.InventoryItemID WHERE dbo.INAssemblyDisassembly.RefType = '210'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PNLR").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet DMTS
    .Open "SELECT dbo.FixedAsset.FixedAssetCode, dbo.FixedAsset.FixedAssetCode, dbo.FixedAsset.SerialNumber, dbo.FixedAsset.FixedAssetName, space(1) as Ten_tieng_anh, space(1) as ĐVt, dbo.FixedAsset.Quantity, dbo.FixedAsset.DeliveryRecordno, dbo.FixedAsset.IncrementDate, dbo.FixedAsset.MadeIn, dbo.FixedAsset.ProductionYear, space(1) as Cong_suat, dbo.FixedAssetCategory.FixedAssetCategoryCode, space(1) as Muc_dich, dbo.FixedAsset.OrgPriceAccount, dbo.Department.DepartmentCode, 'T01' as Ly_do, 'N02' as Nguon_von, dbo.FixedAsset.OrgPrice, dbo.FixedAsset.AccumDepreciationAmount, dbo.FixedAsset.RemainingAmount, dbo.FixedAsset.PurchasePrice, 'C' as Co_tinh_KH, dbo.FixedAsset.DepreciationDate, dbo.FixedAsset.LifeTime, dbo.FixedAsset.MonthPeriodDepreciationAmount, dbo.FixedAsset.DepreciationAccount," & _
        "dbo.FixedAsset.ExpenditureAccount FROM dbo.FixedAsset INNER JOIN dbo.FixedAssetCategory ON dbo.FixedAsset.FixedAssetCategoryID = dbo.FixedAssetCategory.FixedAssetCategoryID INNER JOIN dbo.Department ON dbo.FixedAsset.DepartmentID = dbo.Department.DepartmentID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMTS").Range("A3").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet PTPT
    .Open "SELECT 'PT' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.AccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount, dbo.GeneralLedger.BankAccount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE left(dbo.GeneralLedger.CorrespondingAccountNumber,3)='111' AND dbo.GeneralLedger.CreditAmount<>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("CTPT").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet PTPC
    .Open "SELECT 'PC' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.AccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount, dbo.GeneralLedger.BankAccount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID LEFT JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE left(dbo.GeneralLedger.AccountNumber,3)='111' and dbo.GeneralLedger.CreditAmount<>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("CTPC").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet NHBN
    .Open "SELECT 'BN' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID LEFT JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE dbo.GeneralLedger.BankAccount <> 'NULL' AND left(dbo.GeneralLedger.AccountNumber,3)='112' AND LEFT(dbo.GeneralLedger.CorrespondingAccountNumber,3)<>'112' AND dbo.GeneralLedger.CreditAmount <>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("NHBN").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet NHBC
    .Open "SELECT 'BC' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE dbo.GeneralLedger.BankAccount <> 'NULL' AND left(dbo.GeneralLedger.AccountNumber,3)='112' AND dbo.GeneralLedger.CreditAmount<>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("NHBC").Range("A3").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet NHBN_BC
    '.Open "SELECT 'BN' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC,dbo.GeneralLedger.CreditAmount,dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.SortOrder FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID LEFT JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE dbo.GeneralLedger.BankAccount <> 'NULL' AND left(dbo.GeneralLedger.AccountNumber,3)='112' AND LEFT(dbo.GeneralLedger.CorrespondingAccountNumber,3)='112'"
    .Open "SELECT 'BN' as Ma_chung_tu, dbo.BAInternalTransfer.RefDate, dbo.BAInternalTransfer.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as Ma_san_pham_cong_trinh, space(1) as Ma_doi_tuong, dbo.BAInternalTransfer.JournalMemo, dbo.BAInternalTransferDetail.FromBankAccount, dbo.BAInternalTransferDetail.ToBankAccount, dbo.BAInternalTransferDetail.CurrencyID, dbo.BAInternalTransferDetail.ExchangeRate, dbo.BAInternalTransferDetail.AmountOC, dbo.BAInternalTransferDetail.Amount FROM dbo.BAInternalTransfer INNER JOIN dbo.BAInternalTransferDetail ON dbo.BAInternalTransfer.RefID = dbo.BAInternalTransferDetail.RefID LEFT JOIN dbo.Contract ON dbo.BAInternalTransferDetail.ContractID = dbo.Contract.ContractID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("NHBN_BC").Range("A3").CopyFromRecordset rsPubs
    .Close
  
    ' Lay so lieu ra sheet PXDC
    .Open "SELECT dbo.INTransfer.RefDate, dbo.INTransfer.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INTransfer.JournalMemo, dbo.INTransferDetail.DebitAccount, dbo.Stock.StockCode, Stock1.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INTransferDetail.Unit, dbo.INTransferDetail.Quantity, dbo.INTransferDetail.UnitPrice, dbo.INTransferDetail.Amount FROM dbo.INTransfer INNER JOIN dbo.INTransferDetail ON dbo.INTransfer.RefID = dbo.INTransferDetail.RefID LEFT JOIN dbo.Contract ON dbo.INTransferDetail.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.INTransfer.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.INTransferDetail.FromStockID = dbo.Stock.StockID INNER JOIN dbo.Stock Stock1 ON dbo.INTransferDetail.ToStockID = Stock1.StockID INNER JOIN dbo.InventoryItem ON dbo.INTransferDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PXDC").Range("A3").CopyFromRecordset rsPubs
    .Close


    End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Function
 
Sắp xếp theo thời gian sắp xếp theo bầu chọn
Bạn thử đổi dòng trên thành dòng dưới xem, nếu xài SQL Server thì chạy các câu truy vấn trực tiếp trên SQL cho dễ kiểm tra lỗi, chạy đúng rồi thì mới copy cho vào vba.
Mã: 
strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + m_Database + ";User ID=" + m_UserName + ";Password=" +

Mã: 
strConn = "Driver={SQL Server};Server=" & m_Server & ";Database=" & m_Database & ";Uid=" & m_UserName & ";Pwd=" & m_Password & ";"
 
Upvote 0
Hôm trước mình thấy bạn hỏi trên diễn đàn chỗ này và sửa được rồi nhỉ
 
Upvote 0
gttrongvn đã viết:
Bạn thử đổi dòng trên thành dòng dưới xem, nếu xài SQL Server thì chạy các câu truy vấn trực tiếp trên SQL cho dễ kiểm tra lỗi, chạy đúng rồi thì mới copy cho vào vba.
Mã: 
strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + m_Database + ";User ID=" + m_UserName + ";Password=" +

Mã: 
strConn = "Driver={SQL Server};Server=" & m_Server & ";Database=" & m_Database & ";Uid=" & m_UserName & ";Pwd=" & m_Password & ";"
Nhấp chuột vào đây để mở rộng...
Hi bạn,
Mình chạy vẫn bị lỗi.
Hôm trước mình chạy code như sau thì vẫn okie
Tuy nhiên code này chỉ truy vấn được 1 bảng trong sql. Mình không biết nhiều về sql. Mình đang cố sửa lại code vba lấy dữ liệu từ phần mềm SME 2012 chạy trên sql 2005 để lấy dữ liệu từ phần mềm sql 2008.
Rất mong được sự giúp đỡ từ anh em.
Mã: 
Sub ketnoivoimisa()
     ' Carl SQL Server Connection
     '
     ' FOR THIS CODE TO WORK
     ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
     '

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    Server_Name = "QUANGNGUYENTBB\MISASME2017"
    Database_Name = "MISASME2017SAMPLE"
    User_ID = "sa" ' enter your user ID here
    Password = "123" ' Enter your password herea
    SQLStr = "SELECT * FROM [Bank]"
    

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
    With Worksheets("DMNH").Range("a1:z500")
        .ClearContents
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub
 
Upvote 0
Có khi nào do bạn bỏ code vào function không?
 
Upvote 0
quangnguyentbb đã viết:
Em chào các anh/chị.
Anh/chị cho em hỏi là khi em chạy file vba để lấy dữ liệu từ sql thì phần mềm báo lỗi là "Run time error -2147217865 (80040e37) Automation error" ạ.
Em chưa hiểu tìm lỗi ở đâu vì phần mềm không báo phải debug ở chỗ nào ạ. Em có gửi cả link file ở bên dưới ạ
File gốc :https://drive.google.com/open?id=1mdHZQ1rwg27kiXNfEXoRZmaWXToNWSaT
Mục tiêu của em là lấy dữ liệu từ sql từ phần mềm MISA 2017 ạ
Cảm ơn anh/chị!
Mã: 
Sub Button1_Click()
    DataExtract
End Sub
Function DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

m_Server = Worksheets("DATA").Range("B1").Value
m_UserName = Worksheets("DATA").Range("B2").Value
m_Password = Worksheets("DATA").Range("B3").Value
m_Database = Worksheets("DATA").Range("B4").Value


' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
'strConn = "Provider=SQLOLEDB.1;Data Source=(LOCAL);Initial Catalog=DAICAC;Integrated Security=SSPI"
If (m_UserName = "" And m_Password = "") Then
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + m_Database + ";Integrated Security=SSPI"
Else
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + _
        m_Database + ";User ID=" + m_UserName + ";Password=" + m_Password
End If

'strConn = "Provider=SQLOLEDB.1;Data Source=" + Worksheets("DATA").Range("B1") + ";Initial Catalog=" + Worksheets("DATA").Range("B4") + ";User ID=" + Worksheets("DATA").Range("B2") + ";Password=" + Worksheets("DATA").Range("B3") + ""
         
'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
 
    ' Lay so lieu ra sheet DMDT
    .Open "SELECT dbo.AccountingObject.AccountingObjectCode, dbo.AccountingObject.AccountingObjectName, space(1) as Ten_tieng_anh, dbo.AccountingObject.Address, space(1) as Dia_chi_tieng_anh, dbo.AccountingObject.CompanyTaxCode, space(1) as Doi_tac, space(1) as Doi_tac_tieng_anh, dbo.AccountingObject.Website, dbo.AccountingObject.EmailAddress, dbo.AccountingObject.Tel, dbo.AccountingObject.Fax, dbo.AccountingObject.BankAccount,space(1) as Chu_tk, dbo.AccountingObject.BankName , dbo.AccountingObject.AccountingObjectCategory, 0 AS Loai_Dt FROM dbo.AccountingObject"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMDT").Range("A2").CopyFromRecordset rsPubs
    .Close
   
    ' Lay so lieu ra sheet DMNH
    .Open "SELECT dbo.Bank.BankCode, dbo.Bank.BankName, dbo.Bank.BankNameEngLish,  dbo.Bank.Address, space(1) as Dia_chi_tieng_anh, space(1) as Mst, space(1) as Doi_tac, space(1) as Doi_tac_Ta, space(1) as Website, space(1) as Email, space(1) as Dien_thoai, space(1) as Fax, space(1) as Tk_Nh, space(1) as Chu_Tk, space(1) as Ten_Nh, space(1) as Nhom_Dt, 0 as Loai_Kh FROM dbo.Bank"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMNH").Range("A2").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet DMHD
    .Open "SELECT dbo.Contract.ContractCode, dbo.Contract.ContractName, space(1) as Ten_hop_dong_tieng_anh, dbo.RefType.RefTypeName, dbo.Contract.AccountingObjectBankAccount, dbo.AccountingObject.AccountingObjectCode,  dbo.Contract.SignedDate, dbo.Contract.AmountOC, dbo.Contract.Amount FROM dbo.Contract INNER JOIN   dbo.RefType ON dbo.Contract.RefType = dbo.RefType.RefType INNER JOIN dbo.AccountingObject ON dbo.Contract.AccountingObjectID = dbo.AccountingObject.AccountingObjectID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMHD").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet TONKHOKT
    .Open "SELECT dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode,dbo.InventoryItem.Unit, 1 as He_so_quy_doi, dbo.OpeningInventoryEntry.Quantity, dbo.OpeningInventoryEntry.Quantity, dbo.OpeningInventoryEntry.UnitPrice, dbo.OpeningInventoryEntry.Amount,dbo.OpeningInventoryEntry.PostedDate FROM dbo.OpeningInventoryEntry INNER JOIN dbo.InventoryItem ON dbo.OpeningInventoryEntry.InventoryItemID = dbo.InventoryItem.InventoryItemID INNER JOIN dbo.Stock ON dbo.OpeningInventoryEntry.StockID = dbo.Stock.StockID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("TONKHOKT").Range("A2").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet PNMH
    .Open "SELECT dbo.PUInvoice.PURefDate, dbo.PUInvoice.RefNo, dbo.PUInvoiceDetail.InvDate, dbo.PUInvoiceDetail.InvNo, dbo.PUInvoiceDetail.InvSeries, space(1) as BP, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.PUInvoice.PUJournalMemo, dbo.PUInvoiceDetail.CreditAccount, dbo.Stock.StockCode,  dbo.InventoryItem.InventoryItemCode, dbo.PUInvoiceDetail.Unit, dbo.PUInvoice.CurrencyID, dbo.PUInvoice.ExchangeRate, dbo.PUInvoiceDetail.Quantity, dbo.PUInvoiceDetail.UnitPriceOC, dbo.PUInvoiceDetail.UnitPrice," & _
        "dbo.PUInvoiceDetail.AmountOC, dbo.PUInvoiceDetail.Amount, dbo.PUInvoiceDetail.VATRate, dbo.PUInvoiceDetail.VATAmountOC, dbo.PUInvoiceDetail.VATAmount, dbo.PUInvoiceDetail.ImportTaxAmount FROM dbo.PUInvoice INNER JOIN  dbo.PUInvoiceDetail ON dbo.PUInvoice.RefID = dbo.PUInvoiceDetail.RefID  INNER JOIN  dbo.Contract ON dbo.PUInvoiceDetail.ContractID = dbo.Contract.ContractID INNER JOIN  dbo.AccountingObject ON dbo.PUInvoice.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo" & _
        ".Stock ON dbo.PUInvoiceDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.PUInvoiceDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID "
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PNMH").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet HDBH
    .Open "SELECT dbo.SAInvoice.InvDate, dbo.SAInvoice.InvNo, dbo.SAInvoice.InvSeries, space(1) as BP, space(1) as HD, dbo.AccountingObject.AccountingObjectCode, dbo.SAInvoice.SAJournalMemo, dbo.SAInvoiceDetail.DebitAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.SAInvoiceDetail.Unit, dbo.SAInvoice.CurrencyID, dbo.SAInvoice.ExchangeRate, dbo.SAInvoiceDetail.Quantity, dbo.SAInvoiceDetail.UnitPriceOC, dbo.SAInvoiceDetail.UnitPrice, dbo.SAInvoiceDetail.AmountOC, dbo.SAInvoiceDetail.Amount, dbo.SAInvoiceDetail.VATRate," & _
        "dbo.SAInvoiceDetail.VATAmountOC, dbo.SAInvoiceDetail.VATAmount,space(1) as Gia_von,space(1) as Tien_von,0 as Dinh_khoan FROM dbo.SAInvoice INNER JOIN dbo.AccountingObject ON dbo.SAInvoice.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.SAInvoiceDetail ON dbo.SAInvoice.RefID = dbo.SAInvoiceDetail.RefID INNER JOIN dbo.Stock ON dbo.SAInvoiceDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.SAInvoiceDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID AND dbo.SAInvoiceDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("HDBH").Range("A3").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet HBTL
    .Open "SELECT dbo.SAReturn.RefDate, dbo.SAReturn.RefNo, dbo.SAReturn.InvDate, dbo.SAReturn.InvNo, dbo.SAReturn.InvSeries, space(1) as BP, space(1) as HD, dbo.AccountingObject.AccountingObjectCode, dbo.SAReturn.JournalMemo, dbo.SAReturnDetail.CreditAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.SAReturnDetail.Unit, dbo.SAReturn.CurrencyID, dbo.SAReturn.ExchangeRate, dbo.SAReturnDetail.Quantity, dbo.SAReturnDetail.UnitPriceOC, dbo.SAReturnDetail.UnitPrice, dbo.SAReturnDetail.AmountOC," & _
        "dbo.SAReturnDetail.Amount, dbo.SAReturnDetail.VATRate, dbo.SAReturnDetail.VATAmountOC, dbo.SAReturnDetail.VATAmount FROM dbo.SAReturnDetail INNER JOIN dbo.SAReturn ON dbo.SAReturn.RefID = dbo.SAReturnDetail.RefID INNER JOIN dbo.AccountingObject ON dbo.SAReturn.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.SAReturnDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.SAReturnDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID where dbo.SAReturn.RefType='354'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("HBTL").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet DMVT
    .Open "SELECT dbo.InventoryItem.InventoryItemCode, dbo.InventoryItem.InventoryItemName, space(1) as Ten_tieng_anh, dbo.InventoryItem.Unit, space(1) as Dvt_tieng_anh, dbo.InventoryItem.UnitPrice, dbo.InventoryItem.SalePrice, dbo.InventoryItem.ConvertUnit, dbo.InventoryItem.ConvertRate, space(1) as Gia_mua_1, space(1) as Gia_ban_1, space(1) as Dv_quy_doi_2, space(1) as He_so_quy_doi_2, space(1) as Gia_mua_2, space(1) as Gia_ban_2, space(1) as Dv_quy_doi_3, space(1) as He_so_quy_doi_3, space(1) as Gia_mua_3, space(1) as Gia_ban_3," & _
        "space(1) as Dv_quy_doi_4, space(1) as He_so_quy_doi_4, space(1) as Gia_mua_4, space(1) as Gia_ban_4, space(1) as Loai_Vt,  dbo.InventoryItemCategory.InventoryCategoryCode, dbo.InventoryItem.MinimumStock, space(1) as Sl_ton_toi_da, dbo.InventoryItem.InventoryAccount, dbo.InventoryItem.COGSAccount, dbo.InventoryItem.SaleAccount,space(1) as Tk_Ck, space(1) as Tk_HBTL, space(1) as Ma_Sp, space(1) as Nhom_Dm_Sp, dbo.InventoryItem.TaxRate FROM dbo.InventoryItem INNER JOIN dbo.InventoryItemCategory ON dbo.InventoryItem.InventoryCategoryID = dbo.InventoryItemCategory.InventoryCategoryID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMVT").Range("A2").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet SDDKTK
    .Open "SELECT dbo.OpeningAccountEntry.PostedDate, space(1) as So_chung_tu,space(1) as Ma_bo_phan,dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, space(1) as Dien_giai_tieng_viet,space(1) as Dien_giai_tieng_anh,dbo.OpeningAccountEntry.CurrencyID, dbo.OpeningAccountEntry.ExchangeRate, dbo.OpeningAccountEntry.AccountNumber, dbo.OpeningAccountEntry.DebitAmount, dbo.OpeningAccountEntry.CreditAmount,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.DebitAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_no_ngoai_te,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.CreditAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_co_ngoai_te,space(1) as Han_thanh_toan, dbo.OpeningAccountEntry.DebitAmount," & _
        "dbo.OpeningAccountEntry.CreditAmount,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.DebitAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_no_ngoai_te,CASE WHEN ISNULL(dbo.OpeningAccountEntry.ExchangeRate, 0) != 1 THEN ROUND(dbo.OpeningAccountEntry.CreditAmount / dbo.OpeningAccountEntry.ExchangeRate, 0) ELSE 0 END as Du_co_ngoai_te FROM dbo.OpeningAccountEntry LEFT OUTER JOIN dbo.AccountingObject ON dbo.OpeningAccountEntry.AccountingObjectID = dbo.AccountingObject.AccountingObjectID LEFT OUTER JOIN dbo.Contract ON dbo.OpeningAccountEntry.ContractID = dbo.Contract.ContractID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("SDDKTK").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet PNTP
    .Open "SELECT dbo.INInwardOutward.PostedDate, dbo.INInwardOutward.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INInwardOutward.JournalMemo, dbo.INInwardOutwardDetail.DebitAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INInwardOutwardDetail.Unit, dbo.INInwardOutwardDetail.Quantity FROM dbo.INInwardOutward LEFT OUTER JOIN dbo.INInwardOutwardDetail ON dbo.INInwardOutward.RefID = dbo.INInwardOutwardDetail.RefID LEFT OUTER JOIN dbo.Contract ON dbo.INInwardOutwardDetail.ContractID = dbo.Contract.ContractID LEFT OUTER JOIN dbo.AccountingObject ON dbo.AccountingObject.AccountingObjectID = dbo.INInwardOutward.AccountingObjectID LEFT OUTER JOIN dbo.Stock ON dbo.INInwardOutwardDetail.StockID = dbo.Stock.StockID LEFT OUTER JOIN dbo.InventoryItem ON dbo.INInwardOutwardDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID WHERE dbo.INInwardOutward.RefType = '201'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PNTP").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet PXLR
    .Open "SELECT dbo.INAssemblyDisassembly.INPostedDate, dbo.INAssemblyDisassembly.INRefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INAssemblyDisassembly.INJournalMemo, dbo.INAssemblyDisassemblyDetail.DebitAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INAssemblyDisassembly.Quantity, Stock1.StockCode, InventoryItem1.InventoryItemCode, dbo.INAssemblyDisassemblyDetail.Unit, dbo.INAssemblyDisassemblyDetail.Quantity, dbo.INAssemblyDisassemblyDetail.UnitPrice," & _
        "dbo.INAssemblyDisassemblyDetail.Amount FROM dbo.INAssemblyDisassembly INNER JOIN dbo.INAssemblyDisassemblyDetail ON dbo.INAssemblyDisassembly.RefID = dbo.INAssemblyDisassemblyDetail.RefID LEFT OUTER JOIN dbo.Contract ON dbo.INAssemblyDisassemblyDetail.ContractID = dbo.Contract.ContractID LEFT OUTER JOIN dbo.AccountingObject ON dbo.INAssemblyDisassembly.INAccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.INAssemblyDisassembly.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.INAssemblyDisassembly.InventoryItemID = dbo.InventoryItem.InventoryItemID INNER JOIN dbo.Stock Stock1 ON dbo.INAssemblyDisassemblyDetail.StockID = Stock1.StockID INNER JOIN dbo.InventoryItem InventoryItem1 ON dbo.INAssemblyDisassemblyDetail.InventoryItemID = InventoryItem1.InventoryItemID WHERE dbo.INAssemblyDisassembly.RefType = '211'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PXLR").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet PNLR
    .Open "SELECT dbo.INAssemblyDisassembly.INRefDate, dbo.INAssemblyDisassembly.INRefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INAssemblyDisassembly.INJournalMemo, dbo.INAssemblyDisassembly.CreditAccount, dbo.Stock.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INAssemblyDisassemblyDetail.Quantity, Stock1.StockCode, InventoryItem1.InventoryItemCode, InventoryItem1.Unit, dbo.INAssemblyDisassembly.Quantity, dbo.INAssemblyDisassembly.UnitPrice," & _
        "dbo.INAssemblyDisassembly.Amount FROM dbo.INAssemblyDisassembly INNER JOIN dbo.INAssemblyDisassemblyDetail ON dbo.INAssemblyDisassembly.RefID = dbo.INAssemblyDisassemblyDetail.RefID LEFT OUTER JOIN dbo.Contract ON dbo.INAssemblyDisassemblyDetail.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.INAssemblyDisassembly.INAccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.INAssemblyDisassemblyDetail.StockID = dbo.Stock.StockID INNER JOIN dbo.InventoryItem ON dbo.INAssemblyDisassemblyDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID INNER JOIN dbo.Stock Stock1 ON dbo.INAssemblyDisassembly.StockID = Stock1.StockID INNER JOIN dbo.InventoryItem InventoryItem1 ON dbo.INAssemblyDisassembly.InventoryItemID = InventoryItem1.InventoryItemID WHERE dbo.INAssemblyDisassembly.RefType = '210'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PNLR").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet DMTS
    .Open "SELECT dbo.FixedAsset.FixedAssetCode, dbo.FixedAsset.FixedAssetCode, dbo.FixedAsset.SerialNumber, dbo.FixedAsset.FixedAssetName, space(1) as Ten_tieng_anh, space(1) as ĐVt, dbo.FixedAsset.Quantity, dbo.FixedAsset.DeliveryRecordno, dbo.FixedAsset.IncrementDate, dbo.FixedAsset.MadeIn, dbo.FixedAsset.ProductionYear, space(1) as Cong_suat, dbo.FixedAssetCategory.FixedAssetCategoryCode, space(1) as Muc_dich, dbo.FixedAsset.OrgPriceAccount, dbo.Department.DepartmentCode, 'T01' as Ly_do, 'N02' as Nguon_von, dbo.FixedAsset.OrgPrice, dbo.FixedAsset.AccumDepreciationAmount, dbo.FixedAsset.RemainingAmount, dbo.FixedAsset.PurchasePrice, 'C' as Co_tinh_KH, dbo.FixedAsset.DepreciationDate, dbo.FixedAsset.LifeTime, dbo.FixedAsset.MonthPeriodDepreciationAmount, dbo.FixedAsset.DepreciationAccount," & _
        "dbo.FixedAsset.ExpenditureAccount FROM dbo.FixedAsset INNER JOIN dbo.FixedAssetCategory ON dbo.FixedAsset.FixedAssetCategoryID = dbo.FixedAssetCategory.FixedAssetCategoryID INNER JOIN dbo.Department ON dbo.FixedAsset.DepartmentID = dbo.Department.DepartmentID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("DMTS").Range("A3").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet PTPT
    .Open "SELECT 'PT' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.AccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount, dbo.GeneralLedger.BankAccount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE left(dbo.GeneralLedger.CorrespondingAccountNumber,3)='111' AND dbo.GeneralLedger.CreditAmount<>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("CTPT").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet PTPC
    .Open "SELECT 'PC' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.AccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount, dbo.GeneralLedger.BankAccount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID LEFT JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE left(dbo.GeneralLedger.AccountNumber,3)='111' and dbo.GeneralLedger.CreditAmount<>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("CTPC").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet NHBN
    .Open "SELECT 'BN' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID LEFT JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE dbo.GeneralLedger.BankAccount <> 'NULL' AND left(dbo.GeneralLedger.AccountNumber,3)='112' AND LEFT(dbo.GeneralLedger.CorrespondingAccountNumber,3)<>'112' AND dbo.GeneralLedger.CreditAmount <>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("NHBN").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet NHBC
    .Open "SELECT 'BC' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC," & _
        "dbo.GeneralLedger.CreditAmount FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE dbo.GeneralLedger.BankAccount <> 'NULL' AND left(dbo.GeneralLedger.AccountNumber,3)='112' AND dbo.GeneralLedger.CreditAmount<>'0'"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("NHBC").Range("A3").CopyFromRecordset rsPubs
    .Close

    ' Lay so lieu ra sheet NHBN_BC
    '.Open "SELECT 'BN' as Ma_chung_tu, dbo.GeneralLedger.RefDate, dbo.GeneralLedger.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as SP, dbo.AccountingObject.AccountingObjectCode, dbo.GeneralLedger.JournalMemo, dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.CorrespondingAccountNumber, dbo.GeneralLedger.CurrencyID, dbo.GeneralLedger.ExchangeRate, dbo.GeneralLedger.CreditAmountOC,dbo.GeneralLedger.CreditAmount,dbo.GeneralLedger.BankAccount, dbo.GeneralLedger.SortOrder FROM dbo.GeneralLedger LEFT JOIN dbo.Contract ON dbo.GeneralLedger.ContractID = dbo.Contract.ContractID LEFT JOIN dbo.AccountingObject ON dbo.GeneralLedger.AccountingObjectID = dbo.AccountingObject.AccountingObjectID WHERE dbo.GeneralLedger.BankAccount <> 'NULL' AND left(dbo.GeneralLedger.AccountNumber,3)='112' AND LEFT(dbo.GeneralLedger.CorrespondingAccountNumber,3)='112'"
    .Open "SELECT 'BN' as Ma_chung_tu, dbo.BAInternalTransfer.RefDate, dbo.BAInternalTransfer.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, space(1) as Ma_san_pham_cong_trinh, space(1) as Ma_doi_tuong, dbo.BAInternalTransfer.JournalMemo, dbo.BAInternalTransferDetail.FromBankAccount, dbo.BAInternalTransferDetail.ToBankAccount, dbo.BAInternalTransferDetail.CurrencyID, dbo.BAInternalTransferDetail.ExchangeRate, dbo.BAInternalTransferDetail.AmountOC, dbo.BAInternalTransferDetail.Amount FROM dbo.BAInternalTransfer INNER JOIN dbo.BAInternalTransferDetail ON dbo.BAInternalTransfer.RefID = dbo.BAInternalTransferDetail.RefID LEFT JOIN dbo.Contract ON dbo.BAInternalTransferDetail.ContractID = dbo.Contract.ContractID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("NHBN_BC").Range("A3").CopyFromRecordset rsPubs
    .Close
 
    ' Lay so lieu ra sheet PXDC
    .Open "SELECT dbo.INTransfer.RefDate, dbo.INTransfer.RefNo, space(1) as Bo_phan, dbo.Contract.ContractCode, dbo.AccountingObject.AccountingObjectCode, dbo.INTransfer.JournalMemo, dbo.INTransferDetail.DebitAccount, dbo.Stock.StockCode, Stock1.StockCode, dbo.InventoryItem.InventoryItemCode, dbo.INTransferDetail.Unit, dbo.INTransferDetail.Quantity, dbo.INTransferDetail.UnitPrice, dbo.INTransferDetail.Amount FROM dbo.INTransfer INNER JOIN dbo.INTransferDetail ON dbo.INTransfer.RefID = dbo.INTransferDetail.RefID LEFT JOIN dbo.Contract ON dbo.INTransferDetail.ContractID = dbo.Contract.ContractID INNER JOIN dbo.AccountingObject ON dbo.INTransfer.AccountingObjectID = dbo.AccountingObject.AccountingObjectID INNER JOIN dbo.Stock ON dbo.INTransferDetail.FromStockID = dbo.Stock.StockID INNER JOIN dbo.Stock Stock1 ON dbo.INTransferDetail.ToStockID = Stock1.StockID INNER JOIN dbo.InventoryItem ON dbo.INTransferDetail.InventoryItemID = dbo.InventoryItem.InventoryItemID"
    ' Copy the records into cell A1 on Sheet1.
     Worksheets("PXDC").Range("A3").CopyFromRecordset rsPubs
    .Close


    End With
cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Function
Nhấp chuột vào đây để mở rộng...
Thử lại như sau coi nó báo lỗi không nhé:

Mã: 
Sub Button1_Click()
    DataExtract
End Sub
Function DataExtract()
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    m_Server = "MR_CONG\SQL2K8" 'Worksheets("DATA").Range("B1").Value
    m_UserName = "sa" 'Worksheets("DATA").Range("B2").Value
    m_Password = "sql2000" 'Worksheets("DATA").Range("B3").Value
    m_Database = "MISASME2012Sample" 'Worksheets("DATA").Range("B4").Value
    Dim strConn As String
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + _
        m_Database + ";User ID=" + m_UserName + ";Password=" + m_Password
    cnPubs.Open strConn

    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
    
    With rsPubs
        .ActiveConnection = cnPubs
        .Open "SELECT * FROM [AccountingObject]"
         Worksheets("DMDT").Range("A2").CopyFromRecordset rsPubs
        .Close
        .Open "SELECT * FROM [Bank]"
         Worksheets("DMNH").Range("A2").CopyFromRecordset rsPubs
        .Close
    
    End With
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing

End Function
 
Upvote 0
Hai Lúa Miền Tây đã viết:
Thử lại như sau coi nó báo lỗi không nhé:

Mã: 
Sub Button1_Click()
    DataExtract
End Sub
Function DataExtract()
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    m_Server = "MR_CONG\SQL2K8" 'Worksheets("DATA").Range("B1").Value
    m_UserName = "sa" 'Worksheets("DATA").Range("B2").Value
    m_Password = "sql2000" 'Worksheets("DATA").Range("B3").Value
    m_Database = "MISASME2012Sample" 'Worksheets("DATA").Range("B4").Value
    Dim strConn As String
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + _
        m_Database + ";User ID=" + m_UserName + ";Password=" + m_Password
    cnPubs.Open strConn

    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset
   
    With rsPubs
        .ActiveConnection = cnPubs
        .Open "SELECT * FROM [AccountingObject]"
         Worksheets("DMDT").Range("A2").CopyFromRecordset rsPubs
        .Close
        .Open "SELECT * FROM [Bank]"
         Worksheets("DMNH").Range("A2").CopyFromRecordset rsPubs
        .Close
   
    End With
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing

End Function
Nhấp chuột vào đây để mở rộng...
Em có thủ thì excel báo lỗi sau ạ
1541745837328.png
1541745868922.png
Bài đã được tự động gộp:

vanaccex đã viết:
Mình không Có phần mềm misa nên không test được
Nhấp chuột vào đây để mở rộng...
Hi bạn,
Nếu bạn không phiền có thể cài PM MISA 2017 ở: http://forum.misa.com.vn/threads/th...-pham-misa-sme-net-2017-r44-18-10-2018.13496/
 
Lần chỉnh sửa cuối:
Upvote 0
Upvote 0
Hai Lúa Miền Tây đã viết:
Thử lại như sau coi nó báo lỗi không nhé:

Mã: 
Sub Button1_Click()
    DataExtract
End Sub
Function DataExtract()
    ' Create a connection object.
    Dim cnPubs As ADODB.Connection
    Set cnPubs = New ADODB.Connection

    m_Server = "MR_CONG\SQL2K8" 'Worksheets("DATA").Range("B1").Value
    m_UserName = "sa" 'Worksheets("DATA").Range("B2").Value
    m_Password = "sql2000" 'Worksheets("DATA").Range("B3").Value
    m_Database = "MISASME2012Sample" 'Worksheets("DATA").Range("B4").Value
    Dim strConn As String
    strConn = "Provider=SQLOLEDB.1;Data Source=" + m_Server + ";Initial Catalog=" + _
        m_Database + ";User ID=" + m_UserName + ";Password=" + m_Password
    cnPubs.Open strConn

    Dim rsPubs As ADODB.Recordset
    Set rsPubs = New ADODB.Recordset

    With rsPubs
        .ActiveConnection = cnPubs
        .Open "SELECT * FROM [AccountingObject]"
         Worksheets("DMDT").Range("A2").CopyFromRecordset rsPubs
        .Close
        .Open "SELECT * FROM [Bank]"
         Worksheets("DMNH").Range("A2").CopyFromRecordset rsPubs
        .Close

    End With
    cnPubs.Close
    Set rsPubs = Nothing
    Set cnPubs = Nothing

End Function
Nhấp chuột vào đây để mở rộng...
Bài đã được tự động gộp:

Hai Lúa Miền Tây đã viết:
Bạn kiểm tra lại cái bảng AccountingObject có trong Server của bạn không nhé.
Nhấp chuột vào đây để mở rộng...
Dạ em kiểm tra do bảng đó bị sai tên khi viết hàm.
Cho em hỏi là nếu trong bảng bank có các trường sau:
SQL: 
SELECT TOP 1000 [BankID]

      ,[BankCode]

      ,[BankName]

      ,[BankNameEnglish]

      ,[Address]

      ,[Description]

      ,[Icon]

      ,[Inactive]

      ,[CreatedBy]

      ,[CreatedDate]

      ,[ModifiedBy]

      ,[ModifiedDate]

      ,[EBankCode]

  FROM [MISASME2017Sample].[dbo].[Bank]
Nhưng em chỉ muốn lấy Bankcode và Bankname thì lấy thế nào ạ.
Thứ 2 là em muốn kết quả trả về trên excel cột Banknam sẽ đứng trước cột BankCode ạ.
Thì em sẽ phải điều chỉnh code thế nào ạ
Cảm ơn anh
 
Upvote 0
quangnguyentbb đã viết:
Bài đã được tự động gộp:


Dạ em kiểm tra do bảng đó bị sai tên khi viết hàm.
Cho em hỏi là nếu trong bảng bank có các trường sau:
SQL: 
SELECT TOP 1000 [BankID]

      ,[BankCode]

      ,[BankName]

      ,[BankNameEnglish]

      ,[Address]

      ,[Description]

      ,[Icon]

      ,[Inactive]

      ,[CreatedBy]

      ,[CreatedDate]

      ,[ModifiedBy]

      ,[ModifiedDate]

      ,[EBankCode]

  FROM [MISASME2017Sample].[dbo].[Bank]
Nhưng em chỉ muốn lấy Bankcode và Bankname thì lấy thế nào ạ.
Thứ 2 là em muốn kết quả trả về trên excel cột Banknam sẽ đứng trước cột BankCode ạ.
Thì em sẽ phải điều chỉnh code thế nào ạ
Cảm ơn anh
Nhấp chuột vào đây để mở rộng...
Đơn giản là bạn select nó thôi.

Mã: 
Select [BankName],  [BankCode] From [Bank]
 
Upvote 0
quangnguyentbb đã viết:
Bài đã được tự động gộp:


Dạ em kiểm tra do bảng đó bị sai tên khi viết hàm.
Cho em hỏi là nếu trong bảng bank có các trường sau:
SQL: 
SELECT TOP 1000 [BankID]

      ,[BankCode]

      ,[BankName]

      ,[BankNameEnglish]

      ,[Address]

      ,[Description]

      ,[Icon]

      ,[Inactive]

      ,[CreatedBy]

      ,[CreatedDate]

      ,[ModifiedBy]

      ,[ModifiedDate]

      ,[EBankCode]

  FROM [MISASME2017Sample].[dbo].[Bank]
Nhưng em chỉ muốn lấy Bankcode và Bankname thì lấy thế nào ạ.
Thứ 2 là em muốn kết quả trả về trên excel cột Banknam sẽ đứng trước cột BankCode ạ.
Thì em sẽ phải điều chỉnh code thế nào ạ
Cảm ơn anh
Nhấp chuột vào đây để mở rộng...
Anh có thể cho em xin số điện thoại không? 0929.63.8988
 
Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

maytinhvp01
  • Question
Chuyên đề giải đáp những thắc mắc về code VBA
    • Thích
151 152 153
Trả lời
3K
Đọc
275K
SA_DQ
SA_DQ
hongquan93vn
Tìm công thức có thể đánh STT của giá trị mỗi lần xuất hiện
Trả lời
18
Đọc
112
VetMini
V
Justine Miller
em cần sự trợ giúp
Trả lời
1
Đọc
2
CHAOQUAY
C
8
  • Question
Xin giúp: Nhóm (group data) các dòng trong bảng tính
Trả lời
11
Đọc
399
forcebk
F
hung2412
  • Question
Có Code VBA nào để tự động điều chỉnh tỷ lệ hiển thị màn hình theo thông số Recommended (Theo từng Laptop, PC)?
Trả lời
5
Đọc
116
befaint
befaint
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom