- 01_建立清單
- 02_錄製巨集與修改兩個變數
- 03_增加兩個變數與迴圈
- 04_刪除A欄嶼自動調整欄寬
- 05_完成程式碼
- 06_補上刪除舊的工作表
最常被學員問到的就是如何自動批次下載網路上的資料到EXCEL中，
並且可以自動放在新增的工作表中，
最好可以當下次新增的時候批次刪除舊的工作表，
但要如何做到呢？
首先，要先學會如何如何自動下載YAHOO股市資料到EXCEL中[連結]
接下來要先準備好所需要的連結網址表格，欄位：類股與網址
我是用WORD來轉換格式的，最後轉換成表格即可，
再來就是撰寫(複製)所需程式，其實也都是之前寫好的部分來改就可以，
下載網頁上的那段程式是用錄製巨集而來的只是把它放在迴圈之中，
這樣他就可以反覆幫我們下載資料了。
迴響很大，因此會還有之後更多的之2、3、4....，
這篇主要細部的來說明這樣的自動化下載網路上資源的方法，
首先把巨集錄好，接下來就是把兩個資料變成變數，我用X，Y，
但名稱可以自訂，用X當類股名稱，Y當成網址，
至於有網友會問，為什麼不宣告，因為VBA可以不宣告就可以使用，
那就不需要比較方便，宣告當然也可以啦!
只是因為又要談宣告變數與資料型態，對非資訊背景的網友恐怕有困難。
接下來再加上迴圈範圍，就是清單的頭尾，自動抓到最後一列的位置，
所以可以自動增減範圍了，
並刪除不需要的欄與列，並自動調整欄寬，
當然可以用Columns物件控制就可以了，列物件 Rows物件亦同，
最後在開頭加上，刪除舊的工作表，這樣就完成自動化了。
不足之處還有很多，之後再說明囉!
01_建立清單
02_錄製巨集與修改兩個變數
03_增加兩個變數與迴圈
04_刪除A欄嶼自動調整欄寬
05_完成程式碼
06_補上刪除舊的工作表
不足之處思考：
- 如何快速將網頁中的網址變為表格？
- 如何在下在資料時順便篩選需要的結果(如只下載上漲的資料)
- 如何把改寫成固定時間自動下載資料？
- 如何批次下載股市資料到EXCEL中
- 如何建立表單與將EXCEL當成資料庫
- 如何讓用ACCESS將EXCEL的資料
- 如何排序工作表
- 如何將單次查詢改為批次查詢(EXCEL VBA自動化)
- 如何移動工作表與排序之1
- 如何在EXCEL VBA的工作表隨機上顏色
- 如何快速在EXCEL中建立查詢系統(免資料庫)之2
- 如何精簡VBA程式與傳遞引數
- 如何快速在EXCEL中建立查詢系統(免資料庫)之1
- 如何將大量工作表整合在一個工作表
- 如何在EXCEL VBA新增沒有重複名稱工作表
- 如何自動下載YAHOO股市資料到EXCEL中
- 如何建立表單與將EXCEL當成資料庫使用
- 如何在EXCE VBA中複製工作表
- 如何在EXCEL VBA如何保護工作表
- 如何在EXCEL VBA隱藏與顯示工作表
- 如何在EXCEL VBA新增沒有重複名稱工作表
- 如何在EXCEL工作表中移動
- EXCEL VBA辦公自動化_如何建立查詢系統
https://groups.google.com/forum/#!forum/scu_excel_vba2_83
與前幾期的課程雖然用的是相同的範例，但最大的不同在：
1.除了解說建函數公式，並將之轉成自訂函數，把複雜的公式變簡單。
2.如何將複雜的公式變成簡單的按鈕，按下按鈕就自動完成工作。
內容主要分成：
1.常用函數
2.進階函數應用
3.從函數到VBA設計
4.如何自訂函數
5.VBA程式設計入門
6.如何錄製巨集與修改
7.如何將大量資料轉存到資料庫
8.如何讀取資料庫資料
詳細目錄：http://goo.gl/eQptxx
有講義與範例和完成的畫面公式與程式碼，
只要按照每周的順序學習，學會EXCEL VBA自動化絕非難事，
優點：
1.可非線性學習：可按照自己最不熟的部分多次學習。
2.可反覆學習：有疑問可以多次聽講，保證學的會。
3.可攜帶學習：只要有瀏覽器就可以播放SWF檔，MAC電腦也沒問題。
上課參考用書：
看！就是比你早下班－50個ExcelVBA高手問題解決法
作者：楊玉文 出版社：松崗
出版日期：2011年02月15日 定價：550元
課程理念:
1.以循序漸進的方式, 透過詳細的說明和實用的Excel VBA範例, 逐步了解整個 VBA 的架構與輪廓,進而學習 VBA 變數、函式及邏輯的觀念, 即使沒有任何程式設計基礎, 也能自己親手撰寫 VBA 程序來提昇工作效率, 晉身職場 Excel 高手！ 2.進而解說EXCEL與資料庫的結合，將EXCEL當成資料庫來使用，結合函數、VBA等更深入的功能，讓資料處理和分析的應用更上層樓。 3.將結合GOOGLE雲端試算表，教您如何將EXCEL函數雲端化與網路化。
更多EXCEL VBA連結：
01_EXCEL函數與VBA http://terry28853669.pixnet.net/blog/category/list/1384521
02_EXCEL VBA自動化教學 http://terry28853669.pixnet.net/blog/category/list/1384524
吳老師 2015/1/13
EXCEL,VBA,函數東吳進修推廣部,EXCEL,VBA,函數,程式設計,,指令教學,vba範例教學excel,,excel,vba教學視頻,excel函數教學,excel函數應用,大數據,處理
How to batch download stock data to EXCEL in the 2
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Following the previous share, how to download batches of stock market data to EXCEL in a [link]
Most often asked is how the participants automatic batch downloading information on the web to EXCEL in
And can be automatically placed in the new worksheet,
The best possible time when the next new batch delete the old worksheet,
But how to do it?
First of all, we must first learn how to automatically download data to EXCEL in stock YAHOO [link]
Then be ready first destination URL required form fields: stocks and URLs
I used to convert WORD format, and finally converted to the form can be,
Again is written (copy) required for the program, in fact, are also part to change prior written on it,
That part of the program is on the download page to record macros comes just put it back into the ring,
Over and over again so that he can help us to download the data.
Great response, so there will be more after the 2,3,4 ....,
This main detail to illustrate the method on such automated download Internet resources,
First, the macro record is good, the next is the two materials into a variable, I use X, Y,
But the name can be customized with a name stocks when X, Y as a URL,
As for friends will ask, why not declare because VBA can not declare you can use,
It does not need more convenient, of course, you can declare it!
Just because talk again declare variables and data types, for users of non-IT background, I am afraid there is difficult.
The next loop plus range, the list is end to end, automatic caught last column position
So you can automatically increase or decrease the range, and
And remove unneeded columns and columns, and automatically adjust column widths,
Of course, you can use the Columns object control on it, the column items Rows object likewise,
Finally, at the beginning of add, delete the old worksheet, thus completing automated.
There are many shortcomings, after then explain Hello!
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Teaching Video:
Deficiencies thinking:
How fast will the page URL into the form?
By the way how the next screening results when data needs (such as data download only rise)
How to rewrite a fixed time automatically download data?
Other related learning:
How to batch download stock data in EXCEL
How to create a form with the EXCEL as a repository
How to make use of the information ACCESS to EXCEL
How to sort worksheets
How will a single query to a batch query (EXCEL VBA automation)
How to move a worksheet and sort of a
How random color in EXCEL VBA worksheet
How to quickly build a query system in EXCEL (free library) of 2
VBA program and how to streamline the transfer argument
How to quickly build a query system in EXCEL (free library) of 1
How to integrate a large number of worksheets in a worksheet
How no duplicate names new worksheet in EXCEL VBA
How to automatically download data to EXCEL in stock YAHOO
How to create a form and as a repository using the EXCEL
How do I copy a worksheet in EXCE VBA in
How to protect a worksheet in EXCEL VBA
How to hide and display the worksheet in the EXCEL VBA
How no duplicate names new worksheet in EXCEL VBA
How to move in EXCEL worksheet
EXCEL VBA office automation system _ how to build queries
Teaching Forum:
https://groups.google.com/forum/#!forum/scu_excel_vba2_83
With the previous period, although the use of the course is the same paradigm, but the biggest difference in:
1. In addition to commentary build function formula, and it turned into a custom function, the complex formula becomes simple.
2. How will the complex formula becomes simple button, press the button to automatically complete the work.
The content is divided into:
1. Common Functions
2. Advanced Function
3. VBA function to design
4. How to customize the function
5.VBA introductory programming
6. How to record macros and modification
7. How will dump large amounts of data repositories
8. How to read data repository
Inventory: http: //goo.gl/eQptxx
There are handouts with examples and complete picture of formulas and code,
Just follow the order of a week of learning, not difficult to learn EXCEL VBA automation,
Benefits:
1. Can the nonlinear learning: learning can be repeated in accordance with their most familiar part.
2. Can Repetition: There are questions you can listen several times to ensure that school will be.
3. portable learning: As long as there is a browser can play SWF files, MAC computer, no problem.
Class reference book:
Look! -50 Is more than you had to work a ExcelVBA master problem-solving method
Author: Yang Yuwen Press: Chung
Publication Date: February 15, 2011 Price: 550 yuan
Curriculum ideas:
1. in a progressive manner, through detailed descriptions and practical examples of Excel VBA, develop an understanding of architecture and outline the entire VBA, and then learn the concept of VBA variables, function and logical, even without any programming foundation, but also to their own hands Write a VBA program to improve work efficiency, entry into the workplace Excel expert! 2. then combined with commentary EXCEL database, the database will be used as EXCEL, combining functions, VBA and other more in-depth feature that allows applications to new heights data processing and analysis. 3. GOOGLE cloud will combine spreadsheets, teach you how to EXCEL cloud-based and network-based functions.
More EXCEL VBA link:
01_EXCEL functions and VBA http://terry28853669.pixnet.net/blog/category/list/1384521
02_EXCEL VBA automation teaching http://terry28853669.pixnet.net/blog/category/list/1384524
Wu 2015/1/13
EXCEL, VBA, Soochow education promotion department functions, EXCEL, VBA, function, program design,, instruction teaching, vba examples of teaching excel ,, excel, vba instructional videos, excel function of teaching, excel function application, large data processing
How to batch download stock data to EXCEL in the 2
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Following the previous share, how to download batches of stock market data to EXCEL in a [link]
Most often asked is how the participants automatic batch downloading information on the web to EXCEL in
And can be automatically placed in the new worksheet,
The best possible time when the next new batch delete the old worksheet,
But how to do it?
First of all, we must first learn how to automatically download data to EXCEL in stock YAHOO [link]
Then be ready first destination URL required form fields: stocks and URLs
I used to convert WORD format, and finally converted to the form can be,
Again is written (copy) required for the program, in fact, are also part to change prior written on it,
That part of the program is on the download page to record macros comes just put it back into the ring,
Over and over again so that he can help us to download the data.
Great response, so there will be more after the 2,3,4 ....,
This main detail to illustrate the method on such automated download Internet resources,
First, the macro record is good, the next is the two materials into a variable, I use X, Y,
But the name can be customized with a name stocks when X, Y as a URL,
As for friends will ask, why not declare because VBA can not declare you can use,
It does not need more convenient, of course, you can declare it!
Just because talk again declare variables and data types, for users of non-IT background, I am afraid there is difficult.
The next loop plus range, the list is end to end, automatic caught last column position
So you can automatically increase or decrease the range, and
And remove unneeded columns and columns, and automatically adjust column widths,
Of course, you can use the Columns object control on it, the column items Rows object likewise,
Finally, at the beginning of add, delete the old worksheet, thus completing automated.
There are many shortcomings, after then explain Hello!
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Teaching Video:
Deficiencies thinking:
How fast will the page URL into the form?
By the way how the next screening results when data needs (such as data download only rise)
How to rewrite a fixed time automatically download data?
Other related learning:
How to batch download stock data in EXCEL
How to create a form with the EXCEL as a repository
How to make use of the information ACCESS to EXCEL
How to sort worksheets
How will a single query to a batch query (EXCEL VBA automation)
How to move a worksheet and sort of a
How random color in EXCEL VBA worksheet
How to quickly build a query system in EXCEL (free library) of 2
VBA program and how to streamline the transfer argument
How to quickly build a query system in EXCEL (free library) of 1
How to integrate a large number of worksheets in a worksheet
How no duplicate names new worksheet in EXCEL VBA
How to automatically download data to EXCEL in stock YAHOO
How to create a form and as a repository using the EXCEL
How do I copy a worksheet in EXCE VBA in
How to protect a worksheet in EXCEL VBA
How to hide and display the worksheet in the EXCEL VBA
How no duplicate names new worksheet in EXCEL VBA
How to move in EXCEL worksheet
EXCEL VBA office automation system _ how to build queries
Teaching Forum:
https://groups.google.com/forum/#!forum/scu_excel_vba2_83
With the previous period, although the use of the course is the same paradigm, but the biggest difference in:
1. In addition to commentary build function formula, and it turned into a custom function, the complex formula becomes simple.
2. How will the complex formula becomes simple button, press the button to automatically complete the work.
The content is divided into:
1. Common Functions
2. Advanced Function
3. VBA function to design
4. How to customize the function
5.VBA introductory programming
6. How to record macros and modification
7. How will dump large amounts of data repositories
8. How to read data repository
Inventory: http: //goo.gl/eQptxx
There are handouts with examples and complete picture of formulas and code,
Just follow the order of a week of learning, not difficult to learn EXCEL VBA automation,
Benefits:
1. Can the nonlinear learning: learning can be repeated in accordance with their most familiar part.
2. Can Repetition: There are questions you can listen several times to ensure that school will be.
3. portable learning: As long as there is a browser can play SWF files, MAC computer, no problem.
Class reference book:
Look! -50 Is more than you had to work a ExcelVBA master problem-solving method
Author: Yang Yuwen Press: Chung
Publication Date: February 15, 2011 Price: 550 yuan
Curriculum ideas:
1. in a progressive manner, through detailed descriptions and practical examples of Excel VBA, develop an understanding of architecture and outline the entire VBA, and then learn the concept of VBA variables, function and logical, even without any programming foundation, but also to their own hands Write a VBA program to improve work efficiency, entry into the workplace Excel expert! 2. then combined with commentary EXCEL database, the database will be used as EXCEL, combining functions, VBA and other more in-depth feature that allows applications to new heights data processing and analysis. 3. GOOGLE cloud will combine spreadsheets, teach you how to EXCEL cloud-based and network-based functions.
More EXCEL VBA link:
01_EXCEL functions and VBA http://terry28853669.pixnet.net/blog/category/list/1384521
02_EXCEL VBA automation teaching http://terry28853669.pixnet.net/blog/category/list/1384524
Wu 2015/1/13
EXCEL, VBA, Soochow education promotion department functions, EXCEL, VBA, function, program design,, instruction teaching, vba examples of teaching excel ,, excel, vba instructional videos, excel function of teaching, excel function application, large data processing
No comments:
Post a Comment