Truy vấn dữ liệu File Excel lên google spreadsheet (1 người xem)

[viehoai]

Từ Bài viết Dịch thuật đa ngôn ngữ (dựa vào Google Translate). Vậy xin hỏi các anh chị có thể viết code cho sự việc sau được không?
1. Em cần viết code cho file A.xls để copy dữ liệu từ google spreadsheet(chẳng hạn từ link này) vùng A1:B10 sheet1 xuống A1 sheet1 file A.xls
2. Ngược lại Copy dữ liệu từ A1:B10 sheet2 file A.xls dán lên D1 sheet1 google Spreadsheet link trên.
Em thử làm thủ công được, em nghĩ có thể viết code cho việc này được
Mong được các anh chị giúp đỡ. Em xin cảm ơn

 

[ptm0412]

Gởi Viehoai,


Tôi làm bằng tay theo cách import external data from web page thì báo lỗi JavaScript isn't enabled in your browser, so this file can't be opened. Enable and reload.
Mặc dù tôi đã cài đặt java vào IE (IE 10)

Xin hỏi khi làm bằng tay, có phải viehoai làm cách này không? Và khi hiện trang web google spreadsheet thì có phải là ctrl A bảng tính rồi import không?

 

[ndu96081631]

Gởi Viehoai,


Tôi làm bằng tay theo cách import external data from web page thì báo lỗi JavaScript isn't enabled in your browser, so this file can't be opened. Enable and reload.
Mặc dù tôi đã cài đặt java vào IE (IE 10)

Xin hỏi khi làm bằng tay, có phải viehoai làm cách này không? Và khi hiện trang web google spreadsheet thì có phải là ctrl A bảng tính rồi import không?

Cách import external data from web page này dù không báo lỗi cũng không ra kết quả gì đâu sư phụ à
Em nghĩ có lẽ viethoai nói làm "thủ công" có nghĩa là Ctrl + C rồi Ctrl + V thôi

 

[ptm0412]

Mình nghĩ khác, nó báo lỗi this file can't be opened, chứ không phải this webpage can not be imported.

Với 1 số trang web có table data nằm lẫn trong nội dung (không phải bảng tính), vẫn chọn table và import được.

Vả lại nếu ctrl C và Ctrl V thì chẳng thà vào menu File - Download As MS Excel. sẽ có đầy đủ công thức chứ không phải value.

 

[viehoai]

Mình nghĩ khác, nó báo lỗi this file can't be opened,chứ không phải this webpage can not be imported.

Với 1 số trang web có table data nằm lẫn trong nội dung (không phải bảng tính), vẫn chọn table và import được.

Vả lại nếu ctrl C và Ctrl V thì chẳng thà vào menu File - Download As MS Excel. sẽ có đầy đủ công thức chứ không phải value.

Dạ đúng như anh ndu nói em làm thủ công bằng Ctrl+C và Ctrl+V
Vì lý do làm việc trên 1 file với nhiều nơi và cập nhật dữ liệu thường xuyên nên ý tưởng của em là thế. Nếu Download về, cập nhật dữ liệu xong rồi tải lên như thế rất phiền hà và mất thời gian.
Lúc đầu em định hỏi code thực hiện:
1. Download về
2. Xử lý dữ liệu
3. Xóa file cũ trên google spreadsheet
4. Tải lại file lên google spreadsheet
Em nghĩ phương án này càng khó hơn cách trên em đã hỏi.
Hay các anh chị có cách nào khác không? Em xin cảm ơn các anh đã quan tâm.

 

[ndu96081631]

Mình nghĩ khác, nó báo lỗi this file can't be opened,chứ không phải this webpage can not be imported.

Với 1 số trang web có table data nằm lẫn trong nội dung (không phải bảng tính), vẫn chọn table và import được.

Vả lại nếu ctrl C và Ctrl V thì chẳng thà vào menu File - Download As MS Excel. sẽ có đầy đủ công thức chứ không phải value.

Em thì import bình thường, không báo lỗi gì, có điều dữ liệu nhận được là 1 "đám rừng"
-----------------
Search trên google từ khóa: excel vba how to get data from google spreadsheet cũng có rất nhiều bài viết liên quan... nhưng mà... nhức đầu quá

 

[ptm0412]

Làm bằng tay được rồi đây:
http://productforums.google.com/forum/#!topic/docs/7uJXwnJgPhc
(Answer by mikltk)

Step 1: In your Google Docs spreadsheet, click file, then Publish to the Web.
Step 2: Open Excel 2010 (I cannot promise that this works in earlier versions, but you can try or Google it)
Step 3: Click the "DATA" Tab on the top
Step 4: There is a "Get External Data" section, click "from web"
Step 5: Return to your spreadsheet in Google Docs, in the Publish to the Web box, start publishing, then look lower in the box, get a website link to your spreadsheet, copy it.
Step 6: Return to Excel and paste the website into the address bar of the dialog box that opened when you clicked get data from the web
Step 7: Click the check box that appears in the upper left of your website (in the Excel Dialog Box)
Step 8: Import and Enjoy!!

 

[viehoai]

Làm bằng tay được rồi đây:
http://productforums.google.com/forum/#!topic/docs/7uJXwnJgPhc
(Answer by mikltk)

Step 1: In your Google Docs spreadsheet, click file, then Publish to the Web.
Step 2: Open Excel 2010 (I cannot promise that this works in earlier versions, but you can try or Google it)
Step 3: Click the "DATA" Tab on the top
Step 4: There is a "Get External Data" section, click "from web"
Step 5: Return to your spreadsheet in Google Docs, in the Publish to the Web box, start publishing, then look lower in the box, get a website link to your spreadsheet, copy it.
Step 6: Return to Excel and paste the website into the address bar of the dialog box that opened when you clicked get data from the web
Step 7: Click the check box that appears in the upper left of your website (in the Excel Dialog Box)
Step 8: Import and Enjoy!!

Sao em thử mãi vẫn chưa được Anh Mỹ à, nếu được record marcro chắc được đây. Nhưng vấn đề thay đổi dữ liệu trên spreadsheet vẫn chưa có phương án nào Anh nhỉ.
Cảm ơn anh rất nhiều đã quan tâm

 

[ptm0412]

Sao mình làm cái được ngay nhỉ?

- Mở google Spreadsheet
- Mở menu File - Publish to the web
- Nhấn start publishing (chỉ cần làm 1 lần đầu)
- Ngó xuống dưới đáy hộp thoại thấy ô chứa link, copy link
- Mở file excel, vào data - external data - from web
- Hộp thoại mở ra, trong đó có IE đã mở, paste link lúc nãy vào thanh địa chỉ, nhấn Go
- Khi đã load được table (không còn là bảng tính nữa), thì nhấn nút nhỏ màu vàng ở góc trên bên trái của table (không tính nút tương tự của thông báo hướng dẫn). Nút vàng chuyển màu xanh.
- Nhấn nút import.

Xem file đính kèm, đã link vào file google ở bài 1.
Có thể dùng luôn file này link với file google khác bằng cách nhấn chuột phải vào cell bất kỳ của dữ liệu, chọn edit query, copy link publish của file mới paste vào thanh địa chỉ, làm tiếp các bước cuối.

Note:
- Đã thực hiện thành công trên 2003 và 2010
- Sau khi Google Spreadsheet thay đổi, ít nhất 5 phút sau refresh Excel mới thành công.
- Chỉ được quyền chọn publish cả sheet, không được chọn publish 1 vùng dữ liệu
- mất công thức

Tất cả các bước có thể làm ngay trong hộp thoại Get External data của Excel, vì thực chất hộp thoại đó là 1 cửa sổ IE

 

[ptm0412]

Mở file excel trắng bất kỳ và chạy code sau:

Sub Macro1()
    ConString = "https://docs.google.com/a/dcorp.com.vn/spreadsheet" & _
"/pub?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE&"
    
With ActiveSheet.QueryTables.Add(Connection:="URL;" & ConString, Destination:=Range("$A$1"))
        .Name = "Query1"
        .FieldNames = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .Refresh BackgroundQuery:=True
    End With
End Sub

 

[viehoai]

Mở file excel trắng bất kỳ và chạy code sau:

Sub Macro1()
    ConString = "https://docs.google.com/a/dcorp.com.vn/spreadsheet" & _
"/pub?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE&"
    
With ActiveSheet.QueryTables.Add(Connection:="URL;" & ConString, Destination:=Range("$A$1"))
        .Name = "Query1"
        .FieldNames = True
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .Refresh BackgroundQuery:=True
    End With
End Sub

1. Em test code của anh đã ra kết qủa của cả sheet (không như vùng định sẵn như ý muốn), tuy nhiên là dấu hiệu tốt đến đích cần
2. Anh vui lòng giải thích giúp em code của anh là
[GPECODE=vb] ConString = "https://docs.google.com/a/dcorp.com.vn/spreadsheet" & _"/pub?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE&"
[/GPECODE]
Trong khi đó đường lin đến SpreadSheet bài 1 là:
https://docs.google.com/spreadsheet/ccc?ey=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE#gid=0
Vậy nguyên tắc ghi link thế nào là đúng?
Em cảm ơn anh rất nhiều

 

[ptm0412]

1. code của anh đã ra kết qủa của cả sheet (không như vùng định sẵn như ý muốn): Xem bài 9
2. Anh vui lòng giải thích giúp em code của anh là ... : Xem bài 9
3. Các thắc mắc khác: Xem bài 9

(Mà cũng phải, bài 9 chưa nhấn cám ơn tức là chưa đọc ... khà khà)

 

[yeudoi]

Không biết anh viehoai sao không dùng luôn google cho tiện

 

[viehoai]

1. code của anh đã ra kết qủa của cả sheet (không như vùng định sẵn như ý muốn): Xem bài 9
2. Anh vui lòng giải thích giúp em code của anh là ... : Xem bài 9
3. Các thắc mắc khác: Xem bài 9

(Mà cũng phải, bài 9 chưa nhấn cám ơn tức là chưa đọc ... khà khà)

Xin lỗi Anh, thấy file sung sướng tải vè hy vọng được kết quả như ý, thế là QUÊN luôn cả nhấn nút cảm ơn.
Các ý anh nói trên:
1.

Note:
- Chỉ được quyền chọn publish cả sheet, không được chọn publish 1 vùng dữ liệu

Thế là chấp nhận botay.com hả Anh???
2. Đường link bài 1 chỉ là 1 trường hợp cụ thể, khi em sử đường link SpreadSheet khác thì cách viết đường link trong code như thế nào? trong khi đó bài mẫu của anh link đến SpreadSheet bài 1 khác với trong code của Anh mà em nói ở trên
3. Trường hợp code chuyển dữ liệu lên SpreadSheet chưa có phương án nào hả Anh?

Không biết anh viehoai sao không dùng luôn google cho tiện

Anh nói cụ thể hơn được không?
Xin cảm ơn các anh rất nhiều đã giúp đỡ.

 

[ptm0412]

trích bài 9:

- Mở google Spreadsheet
- Mở menu File - Publish to the web
- Nhấn start publishing (chỉ cần làm 1 lần đầu)
- Ngó xuống dưới đáy hộp thoại thấy ô chứa link, copy link



Copy link sẽ được:

https://docs.google.com/spreadsheet/pub?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE&

Nhưng khi paste vào hộp thoại Get External data và load xong thì trở thành:

https://docs.google.com[COLOR=#ff0000]/a/dcorp.com.vn[/COLOR]/spreadsheet/pub?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE&

Chữ màu đỏ là nó thêm vào theo tên miền của mail đăng ký GoogleDrive. Nhưng đừng quan tâm, cứ paste vào sẽ tự nhiên có.

 

[ptm0412]

Tôi thử bỏ đoạn chữ đỏ thì code vẫn chạy. Nghĩa là

constring = https://docs.google.com/spreadsheet/pub?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE&

So với link ban đầu:

https://docs.google.com/spreadsheet/ccc?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE#gid=0

Thì nó thay ccc?key thành pub?key, và thay #gid=0 thành &

Tinh mắt 1 chút sẽ nhận ra thôi.

Vậy sửa code câu lệnh gán constring như sau sẽ không cần bước publish:

constring = Replace(Replace("https://docs.google.com/spreadsheet/ccc?key=0AtuWUkMv4lmjdGtWVWttQ0VWT1VRZUphUTlVbzhYOEE#gid=0", "ccc?", "pub?"), "#gid=0", "&")

Với đoạn text bị replace là link trực tiếp của file GG spreadsheet

 

[yeudoi]

Ý em sao không làm việc luôn trong google spreadsheet mà lại phải copy đến excel rồi lại upload lên lại. Bởi google có phân quyền rất rõ ràng và tính truy xuất rất cao. Đối với việc xử lý số liệu không quá phức tạp thì có thể làm trên nó vô tư

 

[le tin]

Ý của Viehoai như ở #5 , tôi cũng có ý này nhưng chưa làm được . Ví dụ có 2 người làm ở 2 máy với 2 chương trình khác nhau nhưng dùng chung 1 file dữ liệu , cho nên lấy dữ liệu về xử lý xong cập nhật lại để người kia cùng dùng

 

[viehoai]

Sau một thời gian thử nghiệm em rút ra: Chỉ cần impot bằng thủ công 1 lần dữ liệu xuất lên 1 sheet nào đó của file A.xls (mặc dù code import dữ liệu của Anh Mỹ em đã thử nghiệm thành công), sau đó muốn cập nhật dữ liệu record code em được: [GPECODE=vb]Selection.QueryTable.Refresh BackgroundQuery:=False[/GPECODE]. Từ sheet này, ta muốn lấy dữ liệu vùng nào chẳng được.
Như vậy việc lấy dữ liệu từ spreadsheet xuống file excel xem như đã được giải quyết.
Bây giờ còn một vấn đề chưa được giải quyết là: Từ nguồn dữ liệu sheet file excel có cáu trúc giống sheet trên spreadsheet. Cần code để cập nhật dữ liệu từ file excel lên spreadsheet. Mong được các anh chị giúp đưỡ tiếp
Em cảm ơn Anh Mỹ rất nhiều.

 

[yeudoi]

Cái này em nghĩ rất khó đó anh viehoai. Em nghĩ chỉ có import từ google thôi