ADO: Kết nối Excel Database cho công việc kế toán

[ptm0412]

Công nợ Tổng hợp Khách hàng & Nhà cung cấp

Code tương tự như Cân đối phát sinh.

Chọn Tài khoản 131 hoặc 331, chọn ngày bắt đầu và ngày kết thúc.

Vui lòng tải file bài này cho cả công nợ chi tiết và tổng hợp, file trên có chỗ sai.

 

[ptm0412]

Cải tiến file công nợ 1 chút:

Datatemp2 lọc sẵn theo tài khoản công nợ cho ngắn lại:

1. DataOld:

mySql = "SELECT date, tkno, tkco, stien,diengiai, MSkh, hoadon " & Chr(10) & _
"FROM [data]" & Chr(10) & _
"Where ((date < " & Date1 & ") And ((Tkno='" & Tk & "') Or(Tkco='" & Tk & "')))" & Chr(10) & _
"ORDER BY Date;"

2. DataNew:

mySql = "SELECT date, sct, tkno, tkco, stien, diengiai, MSkh, hoadon " & Chr(10) & _
"FROM [data]" & Chr(10) & _
"Where ((date >= " & Date1 & ") And (date<= " & Date2 & "))  And _
((Tkco='" & Tk & "') Or(Tkno='" & Tk & "'))" & Chr(10) & _
"ORDER BY Date;"

Chắc có nhanh hơn, và có lợi 1 điều là nhẹ file hơn, do copy xuống sheet ít hơn hẳn.

Ngoài ra, do trong code có dùng câu lệnh gán công thức tính số dư 2 cột cuối, rồi gán cứng giá trị lại, nên phải bỏ câu Application.Calculation = xlCalculationManual
Vì Excel chưa kịp tính toán đã gán cứng giá trị rồi.

Các bạn tải lại file công nợ dưới đây.

 

[ptm0412]

Sổ Phát sinh Chi tiết Vật tư hàng hóa

Tương tự Công nợ chi tiết
Chọn Tài khoản Vật tư hoặc hàng hóa, chọn mã vật tư, ngày bắt đầu và ngày kết thúc, nhấn nút.

 

[ptm0412]

Bảng Cân đối nhập xuất tồn vật tư hàng hóa.

Code tương tự như Tổng hợp công nợ.

 

[ptm0412]

II Các báo cáo không chính thống
1. Bảng kê hóa đơn nợ của khách hàng

Điều kiện:

- Cơ sở dữ liệu phải có 1 trường "số hóa đơn"
- Khi thu tiền phải ghi rõ thu tiền hóa đơn số mấy vào trường này
- Khi gõ số hóa đơn phải theo quy ước số hóa đơn gồm mấy con số, thiếu phải điền số 0 vào, thí dụ quy ước 7 số, hóa đơn số 0034567. Mục đích để số hóa đơn bán hàng và số hóa đơn thu tiền giống nhau, Excel trừ đúng vào hóa đơn nợ. Gõ sai lão chết tiệt không chịu trách nhiệm.
- Nếu thu tiền nhiều hóa đơn, phải tách ra nhiều dòng, mỗi dòng 1 hóa đơn
- Nếu thu tiền ít hơn 1 hóa đơn, hoặc thu nhiều nhưng trừ dần từng tờ hóa đơn không hết số tiền của tờ hóa đơn cuối, thì lần sau phải thu bù hóa đơn đó.
- Để khỏi nhầm với hóa đơn đầu vào và đầu ra, trường HD tương ứng phải là FALSE

 

[sealand]

Mình thực sự khâm phục công sức và "Tâm" của Ptm. Mong tiếp tục nhé.Nếu được phép của GPE sau này Ptm tổng hợp lại thành chuyên mục thì thật sự bổ ích cho dân Kế toán cần tạo cho riêng mình 1 chương trình Kế toán tham khảo.
Cám ơn.

P/s: Mình tham gia 1 chút. Riêng bài "1. Bảng kê hóa đơn nợ của khách hàng" thì cộtHD không nên để dạng Boolean vì nó chứa ít thông tin quá. Các phần mềm Kế toán thường hay dùng:
Hóa đơn vào:
V05: Hóa đơn 5%
V10: Hóa đơn 10%
V10B: Hóa đơn 10% có phụ phí (Xăng dầu v.v...)
Hóa đơn ra:
R05: Hóa đơn 5%
R10: Hóa đơn 10%
R10B: Hóa đơn 10% có phụ phí (Xăng dầu v.v...)

 

[hai2hai]

- Nếu thu tiền nhiều hóa đơn, phải tách ra nhiều dòng, mỗi dòng 1 hóa đơn
- Nếu thu tiền ít hơn 1 hóa đơn, hoặc thu nhiều nhưng trừ dần từng tờ hóa đơn không hết số tiền của tờ hóa đơn cuối, thì lần sau phải thu bù hóa đơn đó.

- Khi thanh toán, KH thanh toán vượt tổng tất cả các hóa đơn nợ thì sao anh? (số tiền thừa dùng để trả trước hóa đơn lần sau chứ ko trả lại).

- Tương tự, KH trả trước thì sao anh?

 

[ptm0412]

Cám ơn Sealand.

Mình tham gia 1 chút. Riêng bài "1. Bảng kê hóa đơn nợ của khách hàng" thì cộtHD không nên để dạng Boolean

trường HD chỉ để xác định có hoá đơn đầu vào hoặc đầu ra hay không, nhằm mục đích lên 2 bảng kê hoá đơn báo cáo thuế. Ngoài ra còn 1 trường LoaiHD để phân biệt hoá đơn trực tiếp và hoá đơn khấu trừ, 1 trường VATRate là trường thuế suất. Thiết nghĩ như vậy là đủ cho khá nhiều yêu cầu.

Ghi chú: Theo ý kiến riêng của tôi thì chỉ có 2 loại thuế suất 5% và 10%, không có loại thứ 3. Theo nhận định của tôi thì phần mềm nào tách "10% có phụ phí" là thừa mà vẫn không đủ! Lý do:

- Biết đâu sẽ có "5% có phụ phí" ?
- Mục đích của PM là sẽ tự động nhân thuế suất ra tiền thuế cho 2 loại đầu, và không nhân hoặc nhân có điều kiện cho loại thứ 3. Điều này không cần thiết vì theo thực tế, những hoá đơn mười mươi 5%, hoặc 10%, khi tự động nhân xong cũng phải kiểm tra lại và sửa bằng tay một vài đồng, vài chục, thậm chí cả trăm đồng. Cái này tuỳ thuộc vào người viết hoá đơn làm tròn số kiểu nào! Kể cả hoá đơn tự động tính, tự động in cũng lệch vài đồng là chuyện thường.
- Khi tự động nhân như vậy, nếu sơ ý không viết code làm tròn sẽ có số lẻ đằng sau. Cứ vài tờ như vậy là lệch 1 đồng trên báo cáo là chuyện thường xuyên xảy ra. Kiểu như mua hàng 2 tờ hoá đơn, trả tiền 2 tờ đủ, mà vẫn lên báo cáo công nợ tồn đọng!

 

[ptm0412]

- Khi thanh toán, KH thanh toán vượt tổng tất cả các hóa đơn nợ thì sao anh? (số tiền thừa dùng để trả trước hóa đơn lần sau chứ ko trả lại).
- Tương tự, KH trả trước thì sao anh?

Nói chung khi khách hàng trả tiền trước, nghĩa là khi trả tiền chưa có số hoá đơn, thì sau này khi bán hàng xuất hoá đơn sẽ phải quay lại để điền vào. Phần này các phần mềm đều có option trong form nhập liệu:

1. Khi thu tiền khách hàng, chọn option nào đó để cấn trừ hoá đơn, sẽ nhảy ra 1 form liệt kê hoá đơn nợ (nếu có) để đánh dấu trừ nợ từng tờ.

2. Khi bán hàng, chọn option nào đó, sẽ nhảy ra 1 form liệt kê số tiền đã trả trước (nếu có), để điền số hoá đơn vào.

Sau đó PM sẽ tự động làm phần còn lại.

Nói chung đây là vấn đề của nhập liệu, không phải vấn đề của ADO trong topic này. Hơn nữa, mình chỉ có tham vọng làm file Excel ứng dụng cho 1 Doanh nghiệp loại nhỏ, thậm chí không có nhiều kho hàng, không tính giá thành phức tạp. Trong Excel có khi phải sửa trực tiếp vào cells, hoặc đôi khi tính tay ra con số để gõ vào.

Doanh nghiệp lớn phức tạp không nên dùng Excel, mà nên sử dụng phần mềm của người viết chuyên nghiệp hơn, thí dụ VNUNI, không nên dùng của lão chết tiệt. Topic này chỉ để tham khảo cách vận dụng ADO, các thủ thuật Select, Join, TempQuery, Where condition, ... để ra rất nhiều những báo cáo khác nhau.

Muốn làm được thì như đã nói, phải thiết kế được Cơ sở dữ liệu với đầy đủ những tables, fields nhằm đáp ứng yêu cầu của các loại báo cáo. Giỏi lập trình đến đâu mà CSDL không đủ thông tin cần thiết thì cũng thua.
Thí dụ muốn liệt kê hoá đơn nợ như bài trên, 1 yêu cầu đơn giản, nhưng đòi hỏi rất nhiều từ thiết kế CSDL, nhập liệu, đến kỹ năng vận dụng thủ thuật SQL.

Xin cám ơn mọi người đã quan tâm theo dõi.

 

[hai2hai]

Nói chung đây là vấn đề của nhập liệu, không phải vấn đề của ADO trong topic này. Hơn nữa, mình chỉ có tham vọng làm file Excel ứng dụng cho 1 Doanh nghiệp loại nhỏ, thậm chí không có nhiều kho hàng, không tính giá thành phức tạp. Trong Excel có khi phải sửa trực tiếp vào cells, hoặc đôi khi tính tay ra con số để gõ vào.

Doanh nghiệp lớn phức tạp không nên dùng Excel, mà nên sử dụng phần mềm của người viết chuyên nghiệp hơn, thí dụ VNUNI, không nên dùng của lão chết tiệt. Topic này chỉ để tham khảo cách vận dụng ADO, các thủ thuật Select, Join, TempQuery, Where condition, ... để ra rất nhiều những báo cáo khác nhau.

Em hiểu rồi, mới đầu em nghĩ là em đưa thêm các tình huống cho sinh động, té ra không phải là mục tiêu đó mà chỉ là mục tiêu học tập các thủ thuật. Thế thì em sẽ ko bàn tới các tình huống đó nữa vậy

Pre-Payment là 1 tình huống có thực, nghiệp vụ có thật trong thực thế mà đa số các phần mềm kế toán nào cũng phải tính tới (hầu hết các PMKT của nước ngoài đều có phần này). Nó "có bài có vở" để xử lý hẳn hoi chứ ko phải chỉ là chạy được và chỉ là vấn đề nhập liệu (ko chỉ phân bổ pre-payment amount cho các hóa đơn thanh toán)

Đúng là trong tình huống thực tế có nhiều thứ phải bàn. Tỷ như việc thanh toán cho từng hóa đơn rồi (trả trước hay trả sau cũng thế), sau đó dân nhà ta lại thích ... sửa/xóa hóa đơn thì mệt lắm. Lúc đó các phiếu thu chi thanh toán sẽ ra sao? Có ăn theo việc sửa/xóa hóa đơn đó ko? (Khổ nhất là sửa xóa chứng từ thì phải care đủ thứ, còn nếu chỉ có nhập xong và miễn sửa thì rất đơn giản)

Thôi, anh cứ tiếp tục các tình huống mà anh đưa ra đi.

 

[ptm0412]

2. Bảng kê hóa đơn nợ nhà cung cấp:

Kết hợp luôn với sheet Bảng kê hóa đơn nợ khách hàng cho tiện.

 

[ptm0412]

3. Báo cáo Chi phí

Tập hợp chi phí theo tài khoản và loại chi phí.

Ghi chú:

- Dùng 1 Query tách Data từ ngày Date1 đến ngày Date2, chỉ lấy dòng có field mã chi phí <>"", gán xuống sheet DataTemp2, gán Name là "DataCP"
- Dùng 1 vòng lặp For để lấy 5 queries chi phí cho 5 tài khoản: 627, 632, 635, 641, 642, lấy từ "DataCP", gán xuống sheet Tmp4, đặt name tương ứng.
- Dùng 1 Query chính từ bảng DMCP Left join 5 bảng chi phí của 5 tài khoản.
- Điều kiên lọc: Tổng chi phí cùng loại trong cả 5 tài khoản lớn hơn không.

Toàn bộ công việc này tương đương với việc lập 1 Pivot Table, nhưng nhanh hơn và còn có thể lọc theo khoảng thời gian bất kỳ.

 

[ptm0412]

Nhân trong bài này, có bạn muốn in công nợ chi tiết của khách hàng (hoặc nhà cung cấp 1 cách liên tục (chắc để tiết kiệm giấy), tôi có viết:

Với ý tưởng in liên tục, bạn đã dùng Pivot table và kết quả như tôi đã chỉ ra ở trên. Theo tôi, bạn hãy dùng ADO lần lượt cho từng mã đối tượng, copy xuống sheet nối tiếp nhau, cách nhau 1 dòng cộng và 1 dòng số dư cuối kỳ. Dù vậy cũng không phải viết câu SQL 100 lần cho 100 đối tượng, mà chỉ dùng 1 câu SQL duy nhất với biến thay đổi, biến này chạy theo danh sách mã đối tượng.

và:

II. Về việc dùng ADO hay PivotTable:

Tôi vẫn không ủng hộ việc dùng PivotTable như là 1 báo cáo hoàn chỉnh.
Lý do là cấu trúc 1 báo cáo khác xa cấu trúc của Pivot table, thêm dòng thêm cột thì rất gượng ép mà vẫn không đúng.

Hôm nay tôi viết code tạo bảng công nợ chi tiết in liên tục, mặc dù biết rằng có thể in hàng loạt công nợ chi tiết của mọi khách hàng (nhà cung cấp) bằng cách thay lần lượt mã đối tượng vào ô tương ứng của sheet và chạy code, thêm dòng lệnh in. Nhưng như vậy nghĩa là mỗi đối tượng sẽ qua 1 trang in mới dù cho không phát sinh hoặc chỉ phát sinh 1 vài dòng dữ liệu. (Tốn giấy)

ADO chạy rất nhanh, nhưng code bị hơi chậm do chèn dòng tổng, dòng trắng phân cách, dư nợ cuối kỳ cho từng đối tượng. Khoảng vài cái chớp mắt. Nhưng thử nghĩ ADO chạy chừng 30 lần cho 30 đối tượng mà chỉ 3 - 5 giây thì cũng đáng.

 

[levanduyet]

Chào các bạn,

Mới cập nhật
Tin chắc các bạn cần bài tổng hợp nhằm tham khảo. Mạn phép Bác ptm0412 tôi tổng hợp thành tập tin help.
Các bạn có thể tải tập tin tại mediafire.

Chúc các bạn vui.

Lê Văn Duyệt

 

[thanhtv2104]

Bảng Cân đối phát sinh trong khoảng thời gian bất kỳ

1. Chạy code separateData, lấy OldData trước ngày Date1, lấy NewData trong khoảng Date1, Date2.

2. Chạy code CDPSTemp, lấy số dư đầu kỳ (ngày Date1) tức là số dư cuối kỳ của khoảng thời gian từ đầu năm đến trước Date1.

3. Chạy SQL:

SELECT DMTK.SoTK, Sum(IIf([tkno]=[sotk],[Stien],0)) AS PSN
FROM DMTK LEFT JOIN NewData ON DMTK.SoTK = NewData.Tkno
GROUP BY DMTK.SoTK;

lấy tổng phát sinh nợ của các TK, liệt kê mọi TK dù có phát sinh hay không, bằng Left Join. Ghi vào sheet Tmp với name range là PSNo

4. Chạy SQL:

SELECT DMTK.SoTK, Sum(IIf([tkco]=[sotk],[Stien],0)) AS PSC
FROM DMTK LEFT JOIN NewData ON DMTK.SoTK = NewData.Tkco
GROUP BY DMTK.SoTK;

Cũng ghi vào sheet Tmp với name range là PSCo

5. Chạy SQL:

SELECT 1, DMTK.SoTK, DMTK.Ten, CDPSOld.CkyNo AS DKN, 
CDPSOld.CkyCo AS DKC, PSNo.PSN, PSCo.PSC
FROM ((DMTK LEFT JOIN PSNo ON DMTK.SoTK = PSNo.SoTK) LEFT JOIN CDPSOld 
ON DMTK.SoTK = CDPSOld.SoTK) LEFT JOIN PSCo 
ON DMTK.SoTK = PSCo.SoTK
GROUP BY DMTK.SoTK, DMTK.Ten, CDPSOld.CkyNo, CDPSOld.CkyCo, PSNo.PSN, PSCo.PSC
HAVING ((([ckyno]+[ckyco]+[psn]+[psc])>0));

Ghi xuống sheet CDPS, tính số dư cuối kỳ và dòng tổng.

PTM cho mình hỏi chút: Tại sao trong file excel của bạn không có sheet nào tên là DMKT, nhưng trong câu truy vấn sql có dùng bảng DMKT nhỉ? Bạn khai báo bảng DMKT này như thế nào để excel hiểu dc đó là một bảng nhỉ?

 

[ptm0412]

Sao bạn không gõ tiếng Việt có dấu? Bạn sửa lại, nếu không tôi sẽ xoá bài.
ADO lấy dữ liệu từ các bảng (table), không phải chỉ lấy từ tên sheet.
Nếu 1 sheet chỉ chứa 1 bảng, ta có thể dùng tên sheet làm tên table, và ADO hiểu. Nếu thêm $ thì càng tốt thí dụ [Data$]
Nếu 1 sheet chứa nhiều table như sheet DM (3 bảng danh mục), ta sẽ đặt name cho từng vùng, name đó trở thành tên table

 

[thanhtv2104]

Sao bạn không gõ tiếng Việt có dấu? Bạn sửa lại, nếu không tôi sẽ xoá bài.
ADO lấy dữ liệu từ các bảng (table), không phải chỉ lấy từ tên sheet.
Nếu 1 sheet chỉ chứa 1 bảng, ta có thể dùng tên sheet làm tên table, và ADO hiểu. Nếu thêm $ thì càng tốt thí dụ [Data$]
Nếu 1 sheet chứa nhiều table như sheet DM (3 bảng danh mục), ta sẽ đặt name cho từng vùng, name đó trở thành tên table

Thanks ptm0412.
ptm0412 cho mình hỏi thêm một câu nữa là: ngôn ngữ SQL dùng trong ADO giống với ngôn ngữ SQL trong hệ quản trị csdl nào? (myslq, sql sever, ....)
- Nếu có thể bạn giúp mình chỉ ra những câu lệnh, cú pháp chuyên dùng cho ADO đc không?

 

[phungvinhquang]

hỏi về cách thay tên sheet bằng tên vùng

Sao bạn không gõ tiếng Việt có dấu? Bạn sửa lại, nếu không tôi sẽ xoá bài.
ADO lấy dữ liệu từ các bảng (table), không phải chỉ lấy từ tên sheet.
Nếu 1 sheet chỉ chứa 1 bảng, ta có thể dùng tên sheet làm tên table, và ADO hiểu. Nếu thêm $ thì càng tốt thí dụ [Data$]
Nếu 1 sheet chứa nhiều table như sheet DM (3 bảng danh mục), ta sẽ đặt name cho từng vùng, name đó trở thành tên table

Rất cảm ơn anh ptm và các bạn. Kiến thức quả là bổ ích. Chỉ sợ là không đủ trình độ để lĩnh hội hết.

Anh ptm làm ơn nói rõ hơn về việc thay tên sheet băng tên vùng, trong trường hợp sheet co nhiều bảng và đã đặt tên bản.
Mình đã thử như sau:
- để tên sheet thì ok
- Đặt tên vùng là DataRng = Data!$A$1:$B$20 (giả sử vùng Data của mình trong phạm vi đó và dòng 1:1 là tiêu đề)
- Thay Data$ = DataRng
Khi chạy thì lỗi "DataRng$" is not a valid name

 

[ptm0412]

- Thay Data$ = DataRng
Khi chạy thì lỗi "DataRng$" is not a valid name

Thay Data$ bằng DataRng thì phải báo lỗi (nếu có) là DataRng is not a valid name chứ?

Bạn đưa file lỗi đó lên xem?

 

[Hai Lúa Miền Tây]

Rất cảm ơn anh ptm và các bạn. Kiến thức quả là bổ ích. Chỉ sợ là không đủ trình độ để lĩnh hội hết.

Anh ptm làm ơn nói rõ hơn về việc thay tên sheet băng tên vùng, trong trường hợp sheet co nhiều bảng và đã đặt tên bản.
Mình đã thử như sau:
- để tên sheet thì ok
- Đặt tên vùng là DataRng = Data!$A$1:$B$20 (giả sử vùng Data của mình trong phạm vi đó và dòng 1:1 là tiêu đề)
- Thay Data$ = DataRng
Khi chạy thì lỗi "DataRng$" is not a valid name

Đối với vùng dữ liệu được đặt ở dạng là name thì không cần thêm dấu $ vào phía sau cùng. Bạn thử bỏ $ xem coi được không nhé.