VBA nâng cao: Nhúng tập tin vào trang tính hoặc trích xuất chúng nhanh chóng (1 người xem)

[HeSanbi]

Bài viết này chia sẻ cho các bạn cách nhập nhiều loại tệp vào trang tính Excel để lưu trữ tạm thời, sau đó có thể xuất các tập tin đó nhanh chóng ra thư mục, hoặc đọc mảng binary của chúng để có thể truy cập nhanh ghi vào Userform như ảnh hoặc mở tệp nhanh chóng. Nếu bạn lưu trữ tệp trong các thư mục, thì khi mang tệp Excel chia sẻ, hoặc chép sang máy tính khác thì rất bất tiện.

Tại sao lại cần nhúng tập tin vào trang tính Excel?

1. Để lưu trữ tạm thời.
2. Để lưu trữ các ảnh để sau cần vẽ lên Userform.
3. Thuận tiện trong việc di chuyển tệp Excel.
4. Lưu trữ trong trang tính, các tệp được đóng gói gọn nhẹ hơn.
5. Có thể lưu trữ hầu hết các định dạng tệp, kể cả một tệp Excel khác.
6. Và nhiều lợi ích khác

Trong Excel có chức năng giúp bạn nhúng tập tin là mục Text trong Tab Insert, với nút nhấn là Object, sau đó có thể chọn cách nhập một tệp ngoài thư mục vào tệp Excel.

​

Cửa sổ tùy chọn nhập tệp như ảnh sau:

​

Sau khi nhập tập tin sẽ hiển thị trong trang tính là một đối tượng như ảnh sau:

​

---

Bạn cũng có thể nhập tập tin với VBA như sau:

Sub AddFileIntoWorksheet()
  On Error GoTo e
  Dim p$
  p = "D:\book1.xlsx"
  With ActiveSheet.OLEObjects.Add(Filename:=p, Link:=False, DisplayAsIcon:=False)
 
  End With
Exit Sub
e:
 
End Sub

---

Để xuất các tập tin ra thư mục với VBA, cần truy cập đối tượng OLEObject sao chép chúng, và sử dụng Shell.Application để dán chúng vào thư mục như sau:

Sub ExportFileInWorksheet()
  On Error GoTo e
  Dim p$, o
  p = "D:\Export\"
  For Each o In ActiveSheet.OLEObjects
    o.Copy
    CreateObject("Shell.Application").Namespace(CVar(p)).Self.InvokeVerb "Paste"
  Next
Exit Sub
e:
 
End Sub

---

Có một cách khác để giúp xuất tập tin là bung tệp Excel thành thư mục, sử dụng VBA xử lý là vào thư mục xl\embeddings mở các tập tin ở dạng binary và xóa các bit như sau:

"...C:\Users\xxx\AppData\Local\Temp\{E13D038A-77FA-476A-AF14-40EED54DBBBD}\{7CF7A118-B434-4E56-9CA8-F0E3F18628EB}\HookWn_win64.dll ¶ "

Cần xóa đi toàn bộ ký tự phía trước cho đến đường dẫn + 5 ký tự tiếp theo, từ đó sẽ có được tập tin gốc đã nhập vào từ trước.
Các bạn cần lưu ý đoạn này {E13D038A-77FA-476A-AF14-40EED54DBBBD}\{7CF7A118-B434-4E56-9CA8-F0E3F18628EB} các ký tự có thể thay đổi.

Các bước thực hiện:

1. Sử dụng thư viện Shell.Application để bung tập tin Excel.
2. Sử dụng lệnh trong thư viện FileSystem để quét thư mục đã bung.
3. Sử dụng thư viện ADODB.Stream để mở tệp với mảng binary.
4. Dùng thư viện Regexp để tìm cấu trúc như trên để xóa các binary.
5. Sử dụng thư viện ADODB.Stream để lưu lại.

Lý do dùng cách xử lý binary thay cho sao chép OLEObject, là vì lệnh sao chép, là lệnh tác động đến System, chính vì vậy hạn chế, tốt nhất là không nên sử dụng lệnh này trong lập trình.


Bài viết này nhằm giúp các bạn thuận tiện trong việc xử lý tệp bên ngoài khi sử dụng cùng tệp Excel, điều này là cần thiết trong một số trường hợp để hạn chế sự bất tiện trong việc di chuyển hoặc chia sẻ tệp Excel.

 

[HeSanbi]

Thêm một cách khác để có được tệp nhúng mà excel tạo ra trong thư mục tạm (Temp) mà không cần phải sao chép hoặc giải nén.

Đối tượng OLEObject, có phương thức Verb thực hiện mở tệp nhúng và Duplicate thực hiện nhân đôi tệp nhúng, khi một trong hai phương thức này được gọi. EXCEL sẽ tự động tạo ra các tệp tạm cho các tệp nhúng trong thư mục tạm. Lúc này chỉ cần chạy mã để quét tìm đến các tệp nhúng trong thư mục tạm.