READ BIG TEXT FILE

用 LINE INPUT 原本可以讀 TEXT FILE , 但檔案太大會ERROR.

所以用以下直接開檔的方法

Sub READ_BIG_TEXT_FILE()
I = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\test\videos.htm", 1)
Do Until objFile.AtEndOfStream
 strLine = objFile.ReadLine
 ActiveSheet.Cells(I, 2).Value = strLine
 I = I + 1
Loop
objFile.Close
End Sub

SOURCE : http://stackoverflow.com/questions/1376756/superfast-way-to-read-large-files-line-by-line-in-vba-please-critique

以下是參考方法 (對大TEXT FILE 無用)

Sub TEST()
    Dim i As Long
    Dim LineText As String
    Open "C:\test\videos." For Input As #24
    i = 2
    While Not EOF(24)
        Line Input #24, LineText
        ActiveSheet.Cells(i, 2).Value = LineText
        P = Split(Record, ",")
        i = i + 1
    Wend
    Close #24
End Sub

取 webpage 至 硬碟上 (dos command) (~ wget)

download "wget"  (for 32-bit and 64-bit Windows)

http://kemovitra.blogspot.hk/2013/02/download-wget-for-32-bit-and-64-bit.html

wget 指令用法與教學(中文)

http://www.ewdna.com/2012/04/wget.html

example ( in dos command mode)

wget -P C:\test\ http://www.youtube.com/user/ChinaNews360/videos

註解 :
(1)  -P: 指定下載到本機的某個目錄下。 即 "c:\test\"
(2) 在 "http://www.youtube.com/user/ChinaNews360/videos" 取 webpage 下來
(3)    檔案會是 "videos."

TIPS FROM PINKILLER.COM

(1) 在 excel 中 執行 函數方法 

代碼:

Private Sub Worksheet_Change(ByVal Target As Range)  a = Application.Sum(Range("b1:d1"))  Range("a2").Value = a End Sub

application.指令

(2) 當前excel文件 , 所在路徑

代碼: ThisWorkbook.Path

(3) 找尋文件夾中的檔案

找尋文件夾中的檔案 代碼: Sub AAA() Dim xlsFile As String xlsFile = Dir(ActiveWorkbook.Path & "\*.XLS") Do If InStr(1, xlsFile, "總匯") = 0 Then     Cells(([A65536].End(xlUp).Row + 1), 1) = xlsFile  End If   xlsFile = Dir Loop Until Len(xlsFile) = 0 End Sub 代碼: xlsFile = Dir(ActiveWorkbook.Path & "\*.XLS") Dir 用來查出檔案名稱 , 可用 "*" 及 "?" 去查 如找出第一個後, 要查第二個就可 打以下例子 代碼: xlsFile = Dir 代碼: InStr(1, xlsFile, "總匯") Instr 用來查出字串內有沒有包含某個字串 , 如有就會返回大於0的整數 , 找不到就返回0.

(4) 查出最後一行(有資料)的位置

代碼:

[A65536].End(xlUp).Row

查出最後一行(有資料)的位置

是一個好好用工具

代碼:

Cells(([B65536].End(xlUp).Row + 1), 1)

最後一行之下一行位置,用來加新資料時很有用

(5) 用 shell 指令中 target 有 space 的問題

I found the command line switches on Microsoft's website. It was contained in Microsoft Knowledge Base Article - Q241422

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241422

Here is an example of code that I used and tested:


Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close" & Chr(34) & "C:\Apps\PopCap Games\Bejeweled Deluxe\sounds\bad2.mp3" & Chr(34), vbHide


If the path to the file that you want to play has any spaces in it (as your example did with the My Documents), then the entire path and file need to be enclosed in quotation marks (that is what the chr(34) is).

The /play tells the media player to play the file. The /close tells it to close after it's done or it will leave the media player open and hidden and taking up resources.

I hope this helps

用 shell 指令時 , 發現 target 位置有問題 ,
(1) 試用 "%20" 代替 space , failed
(2) 試用 位置 "c:\1.mp3" , 成功
原來 target 來 有 space 就要用 chr(34) ( 代表 " 這個附號 )

VBA - COMBINE 多個檔案成為 一個

VBA code :


Sub Append_Text_Files()


Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject


For i1 = 1 To 3

Set oTS = oFS.OpenTextFile(ActiveWorkbook.Path & "\" & "Doc" & i1 & ".txt", ForReading)
vTemp = oTS.ReadAll

Set oTS1 = oFS.OpenTextFile(ActiveWorkbook.Path & "\" & "Output.txt", ForAppending, True)
oTS1.Write (vTemp)
oTS1.Close
oTS.Close

Next i1

End Sub

上面的程式碼 : 將 Doc1.txt 加 Doc2.txt 加 Doc3.txt , 合成 新檔 "Output.txt"

VBA - Write & Print Different in (.TXT) FILE

VBA CODE "

Sub WriteToATextFile()
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory

 MyFile = ActiveWorkbook.Path & "\" & "whateveryouwant.txt"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'write project info and then a blank line. Note the comma is required
Write #fnum, "I wrote this"
Print #fnum, Sheets("Sheet1").Range("a1")
'use Print when you want the string without quotation marks
Print #fnum, "I printed this OK"
Close #fnum
End Sub

OUTPUT FILE ( whateveryouwant.txt )

"I wrote this"
tooltips[0]=["xman.jpg", "G1(270),G2(198)(2nd)(paid5.0)<br /> <B>G3</B>(b75),G4(530)", {background:"#FFFFFF", color:"black", border:"5px ridge darkblue"}]
I printed this OK

Different in with / without quotation marks

在儲存格內可指定格式 (用text 功能)

用 text 可以 指定數字格式

="The Sum " &TEXT(H7,"HK$#,##0.00_);(HK$#,##0.00)")

Result : "The Sum HK$500,000.00"

用vba讀取在同一資料夾內檔案及其資訊 , 寫在即時運算視窗內

Option Explicit
Sub Get_file_info()
    Dim myFso As Scripting.FileSystemObject
    Dim myFiles As Scripting.Files
    Dim myFile As Scripting.File
    Set myFso = New Scripting.FileSystemObject
    '指定檔案
    Set myFiles = myFso.GetFolder(ThisWorkbook.Path).Files
       
    For Each myFile In myFiles
       
    With myFile
    Debug.Print "製作日:" & .DateCreated
    Debug.Print "最後存取日:" & .DateLastAccessed
    Debug.Print "最後更新日:" & .DateLastModified
    Debug.Print "Root磁碟機名:" & .Drive
    Debug.Print "檔案名稱:" & .Name
    Debug.Print "上層資料夾名稱:" & .ParentFolder
    Debug.Print "路徑:" & .Path
    Debug.Print "DOS用短名稱:" & .ShortName
    Debug.Print "DOS用短路徑:" & .ShortPath
    Debug.Print "大小:" & (.Size / 1024) & "KB"
    Debug.Print "類型:" & .Type
    End With
   
    Next