VBA/Excel/Access/Word/File Path/FreeFile — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 19:33, 26 мая 2010
Содержание
- 1 An Example Using Write # and Input #
- 2 Examples of the VBA Open Statement
- 3 Flexible Separators and Delimiters
- 4 Handling Files with Low-Level File Handling
- 5 Reading Data Strings
- 6 Text Files and File Dialog
- 7 uses the delimiter characters to decide the data type of each item and treat it appropriately:
- 8 Writing to Text Files Using Print
An Example Using Write # and Input #
<source lang="vb">
Sub TestWriteInput()
Dim lOutputFile As Long Dim rg As range Set rg = ThisWorkbook.Worksheets(1).range("a1") lOutputFile = FreeFile Open "C:\Write Example.txt" For Output As #lOutputFile Do Until IsEmpty(rg) Write #lOutputFile, rg.value, _ rg.Offset(0, 1).value, _ rg.Offset(0, 2).value, _ rg.Offset(0, 3).value, _ rg.Offset(0, 4).value, _ rg.Offset(0, 5).value, _ rg.Offset(0, 6).value, _ rg.Offset(0, 7).value Set rg = rg.Offset(1, 0) Loop Set rg = Nothing Close lOutputFile Dim lInputFile As Long Dim v1, v2, v3, v4 Dim v5, v6, v7, v8 Set rg = ThisWorkbook.Worksheets(2).range("a1") rg.CurrentRegion.ClearContents lInputFile = FreeFile Open "C:\Write Example.txt" For Input As lInputFile Do Until EOF(lInputFile) Input #lInputFile, v1, v2, v3, v4, v5, v6, v7, v8 rg.value = v1 rg.Offset(0, 1).value = v2 rg.Offset(0, 2).value = v3 rg.Offset(0, 3).value = v4 rg.Offset(0, 4).value = v5 rg.Offset(0, 5).value = v6 rg.Offset(0, 6).value = v7 rg.Offset(0, 7).value = v8 Set rg = rg.Offset(1, 0) Loop Set rg = Nothing Close lInputFile
End Sub
</source>
Examples of the VBA Open Statement
<source lang="vb">
Sub SimpleOpenExamples()
Dim lInputFile As Long Dim lOutputFile As Long Dim lAppendFile As Long lInputFile = FreeFile Open "C:\MyInputFile.txt" For Input As #lInputFile lOutputFile = FreeFile Open "C:\MyNewOutputFile.txt" For Output As #lOutputFile lAppendFile = FreeFile Open "C:\MyAppendFile.txt" For Append As #lAppendFile Close lInputFile, lOutputFile, lAppendFile
End Sub
</source>
Flexible Separators and Delimiters
<source lang="vb"> Sub WriteStringsWithDelimiters() Dim sLine As String Dim sFName As String "Path and name of text file Dim iFNumber As Integer "File number Dim lRow As Long "Row number in worksheet Const sVS As String = ";" "Variable separator character Const sTD As String = """" "Text delimiter character Const sDD As String = "#" "Date delimiter character sFName = "C:\Delimited.txt" iFNumber = FreeFile Open sFName For Output As #iFNumber lRow = 2 Do With Sheet1 sLine = sDD & format(.cells(lRow, 1), "yyyy-mmm-dd") & sDD & sVS sLine = sLine & sTD & .cells(lRow, 2) & sTD & sVS sLine = sLine & sTD & .cells(lRow, 4) & sTD & sVS sLine = sLine & format(.cells(lRow, 6), "0.00") End With Print #iFNumber, sLine lRow = lRow + 1 Loop Until IsEmpty(Sheet1.cells(lRow, 1)) Close #iFNumber End Sub </source>
Handling Files with Low-Level File Handling
<source lang="vb">
Sub LogErrorText()
Dim intFile As Integer intFile = FreeFile Open CurDir & "\ErrorLog.Txt" For Append Shared As intFile Write #intFile, "LogErrorDemo", Now, Err, Error, CurrentUser() Close intFile
End Sub
</source>
Reading Data Strings
<source lang="vb"> Sub ReadStrings() Dim sLine As String Dim sFName As String "Path and name of text file Dim iFNumber As Integer "File number Dim lRow As Long "Row number in worksheet Dim lColumn As Long "Column number in worksheet Dim vValues As Variant "Hold split values Dim iCount As Integer "Counter sFName = "C:\Strings.txt" iFNumber = FreeFile Open sFName For Input As #iFNumber Do Line Input #iFNumber, sLine vValues = Split(sLine, ";") For iCount = LBound(vValues) To UBound(vValues) Debug.Print vValues(iCount) Next iCount Close #iFNumber End Sub </source>
Text Files and File Dialog
<source lang="vb"> Sub WriteFile() Dim dDate As Date Dim sCustomer As String Dim sProduct As String Dim dPrice As Double Dim sFName As String "Path and name of text file Dim iFNumber As Integer "File number Dim lRow As Long "Row number in worksheet sFName = "C:\Sales.txt" iFNumber = FreeFile Open sFName For Output As #iFNumber lRow = 2 Do With Sheet1 dDate = .cells(lRow, 1) sCustomer = .cells(lRow, 2) sProduct = .cells(lRow, 4) dPrice = .cells(lRow, 6) End With Write #iFNumber, dDate, sCustomer, sProduct, dPrice lRow = lRow + 1 Loop Until IsEmpty(Sheet1.cells(lRow, 1)) Close #iFNumber End Sub </source>
uses the delimiter characters to decide the data type of each item and treat it appropriately:
<source lang="vb"> Sub ReadStringsWithDelimiters() Dim sLine As String Dim sFName As String "Path and name of text file Dim iFNumber As Integer "File number Dim lRow As Long "Row number in worksheet Dim lColumn As Long "Column number in worksheet Dim vValues As Variant Dim vValue As Variant Dim iCount As Integer Const sVS As String = ";" "Variable separator character Const sTD As String = """" "Text delimiter character Const sDD As String = "#" "Date delimiter character sFName = "C:\Delimited.txt" iFNumber = FreeFile Open sFName For Input As #iFNumber Do Line Input #iFNumber, sLine vValues = Split(sLine, sVS) For Each vValue In vValues Select Case Left(vValue, 1) "String Case sTD Debug.Print Mid(vValue, 2, Len(vValue) - 2) "Date Case sDD Debug.Print DateValue(Mid(vValue, 2, Len(vValue) - 2)) "Other Case Else Debug.Print vValue End Select Next vValue "Loop until end of file Loop Until EOF(iFNumber) Close #iFNumber </source>
Writing to Text Files Using Print
<source lang="vb"> Sub WriteStrings() Dim sLine As String Dim sFName As String "Path and name of text file Dim iFNumber As Integer "File number Dim lRow As Long "Row number in worksheet sFName = "C:\Strings.txt" iFNumber = FreeFile Open sFName For Output As #iFNumber lRow = 2 Do With Sheet1 sLine = Format(.Cells(lRow, 1), "yyyy-mmm-dd") & ";" sLine = sLine & .Cells(lRow, 2) & ";" sLine = sLine & .Cells(lRow, 4) & ";" sLine = sLine & Format(.Cells(lRow, 6), "0.00") End With Print #iFNumber, sLine lRow = lRow + 1 Loop Until IsEmpty(Sheet1.Cells(lRow, 1)) Close #iFNumber End Sub </source>