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

K
Nhờ mọi người hoàn thiện giúp công thức
Trả lời
2
Đọc
45
kiendt1973
K
GPE-Trợ Lý
MỪNG NGÀY NHÀ GIÁO VIỆT NAM 20/11/2024
    • Thích
    • Yêu thích
Trả lời
0
Đọc
1
GPE-Trợ Lý
GPE-Trợ Lý
dovanha1994
Giúp e tìm nguyên nhân sai và hướng dẫn e đọc file này với ạ
    • Phẫn nộ
Trả lời
3
Đọc
56
dovanha1994
dovanha1994
N
Giúp sửa mã code
Trả lời
19
Đọc
381
ptm0412
ptm0412
P
Nhờ giúp đỡ hàm lọc dữ liệu theo tỉnh
Trả lời
10
Đọc
153
Parabol
P
CodyPham97
tạo biểu tượng thay thế nhấn đúp chuột
Trả lời
1
Đọc
38
BuiQuangThuan
B
H
Chúc mừng 20/11- ngày tôn vinh nhà giáo Việt Nam.
    • Thích
    • Yêu thích
Trả lời
0
Đọc
40
HUONGHCKT
H
F
  • Question
Nhờ tư vấn cách tạo QR trong Excel
Trả lời
4
Đọc
431
hoangtuan.net
hoangtuan.net
N
Vẽ biểu đồ kết hợp nhiều yếu tố trong excel
Trả lời
7
Đọc
184
Ny Ny 2024
N
20050911
File Excel lỗi ''The file could not be accessed. Try one of the following:.."
Trả lời
9
Đọc
451
bibuzz90
B
Nhật Anh 9x
  • Question
Tính tồn cuối kỳ luỹ kế cho nhiều mã
Trả lời
2
Đọc
82
Nhật Anh 9x
Nhật Anh 9x
H
Hỗ trợ lấy danh sách dữ liệu thỏa mãn điều kiện
Trả lời
1
Đọc
71
bebo021999
B
C
Chia sẻ: Kết quả xổ số Keno
    • Cười
    • Thích
2
Trả lời
20
Đọc
549
ptm0412
ptm0412
nguyen trung cuong
File chứa marco không mở được.
Trả lời
2
Đọc
67
nguyen trung cuong
nguyen trung cuong
huynhnhu1234
Tìm giá vé máy bay một chiều và khứ hồi
Trả lời
1
Đọc
104
hvnhpro
H
nobita994
Trừ số lượng dần theo dòng khi quét Sumif tổng có tên sản phẩm bị trùng.
Trả lời
2
Đọc
72
bebo021999
B
PhanTuHuong
Bàn về môn thể thao bóng bàn nào!
    • Thích
    • Yêu thích
    • Cười
2
Trả lời
37
Đọc
2K
cantl
C
Tuấn Bụng Bự
  • Question
Tìm Bộ 8 số xuất hiện nhiều nhất trong random( 1 - 80)
2
Trả lời
31
Đọc
801
Tuấn Bụng Bự
Tuấn Bụng Bự
S
  • Question
Xin file excel bảng giá quần áo, trang phục, phụ kiện
Trả lời
2
Đọc
75
VetMini
V
P
Tổng hợp dữ liệu từ các sheet theo chọn lọc
Trả lời
19
Đọc
379
VetMini
V
Xem thêm…

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