我刚刚学习如何制作宏,我发现一个宏几乎可以完成我需要它做的事情,即从 Excel 输出文本文件。
我需要它做的是将其以某种.mhd
格式输出,我已经完成了,然后获取写入单元格中的所有数据#fnum
并在 Excel 文件中的每个单元格后放置一个返回值。
本质上我只需要所有数据在文本文件中的特定行。我确信有一种优雅的方法可以解决这个问题,但我似乎找不到。
Sub CreateFile()
Do While Not IsEmpty(ActiveCell.Offset(0, 1))
MyFile = ActiveCell.Value & ".mhd"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'use Print when you want the string without quotation marks
Print #fnum, ActiveCell.Offset(0, 5); " " & ActiveCell.Offset(0, 6); " " & _
ActiveCell.Offset(0, 7); " " & ActiveCell.Offset(0, 8); " " & _
ActiveCell.Offset(0, 9); " " & ActiveCell.Offset(0, 10); " " & _
ActiveCell.Offset(0, 11); " " & ActiveCell.Offset(0, 12); " " & _
ActiveCell.Offset(0, 13); " " & ActiveCell.Offset(0, 14); " " & _
ActiveCell.Offset(0, 15); " " & ActiveCell.Offset(0, 16); " " & _
ActiveCell.Offset(0, 17); " " & ActiveCell.Offset(0, 18); " " & _
ActiveCell.Offset(0, 19); " " & ActiveCell.Offset(0, 20); " " & _
ActiveCell.Offset(0, 21); " " & ActiveCell.Offset(0, 22); " " & _
ActiveCell.Offset(0, 23); " " & ActiveCell.Offset(0, 24); " " & _
ActiveCell.Offset(0, 25); " " & ActiveCell.Offset(0, 26)
Close #fnum
ActiveCell.Offset(1, 0).Select
Loop
End Sub
答案1
你的问题很好,因为你尝试解决问题,并向我们展示了你尝试过的方法。你的问题很糟糕,因为你没有指出哪里出了问题。你的宏运行没有错误。我认为它没有按照你的要求运行,但你没有告诉我们你想要什么。
我创建了一个与您的宏匹配的工作表:
我拿了你的代码并做了一些小的修改:
- 我添加
Option Explicit
并声明了所有变量。 - 我添加了一个语句来获取包含我的工作簿的文件夹的名称,并将此文件夹名称添加到 open 语句中。也许您在工作表中包含了路径名,而不需要这个。
。
Option Explicit
Sub CreateFile()
Dim fnum As Long
Dim MyFile As String
Dim PathCrnt As String
PathCrnt = ActiveWorkbook.Path & "\"
Do While Not IsEmpty(ActiveCell.Offset(0, 1))
MyFile = PathCrnt & ActiveCell.Value & ".mhd"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'use Print when you want the string without quotation marks
Print #fnum, ActiveCell.Offset(0, 5); " " & ActiveCell.Offset(0, 6); " " & _
ActiveCell.Offset(0, 7); " " & ActiveCell.Offset(0, 8); " " & _
ActiveCell.Offset(0, 9); " " & ActiveCell.Offset(0, 10); " " & _
ActiveCell.Offset(0, 11); " " & ActiveCell.Offset(0, 12); " " & _
ActiveCell.Offset(0, 13); " " & ActiveCell.Offset(0, 14); " " & _
ActiveCell.Offset(0, 15); " " & ActiveCell.Offset(0, 16); " " & _
ActiveCell.Offset(0, 17); " " & ActiveCell.Offset(0, 18); " " & _
ActiveCell.Offset(0, 19); " " & ActiveCell.Offset(0, 20); " " & _
ActiveCell.Offset(0, 21); " " & ActiveCell.Offset(0, 22); " " & _
ActiveCell.Offset(0, 23); " " & ActiveCell.Offset(0, 24); " " & _
ActiveCell.Offset(0, 25); " " & ActiveCell.Offset(0, 26)
Close #fnum
ActiveCell.Offset(1, 0).Select
Loop
End Sub
您的宏运行无误,并且每行创建一个文件。我同意这并不优雅,但如果这是您想要的,它就可以工作。我想知道您是否希望将所有行放在一个文件中。如果是这样,您需要在循环之外打开和关闭文件。
下面我整理了你的代码,但我没有改变它的作用。希望这能有所帮助。如果我的任何解释不清楚,请回来。如果这没有给你足够的信息来解决问题,你将不得不更充分地解释你的宏出了什么问题。
Option Explicit
Sub CreateFile2()
Dim ColStart As Long
Dim ColCrnt As Long
Dim FileLine As String
Dim FileName As String
Dim fnum As Long
Dim MyFile As String
Dim PathCrnt As String
Dim RowStart As Long
Dim RowLast As Long
Dim RowCrnt As Long
' Your code starts at the active cell. This relies on the user leaving
' the cursor in the correct cell of the correct worksheet. I have left
' the macro like this but have made it more explicit.
ColStart = ActiveCell.Column
RowStart = ActiveCell.Row
PathCrnt = ActiveWorkbook.Path & "\"
' My code does not move the cursor and operates on the worksheet
' identified in the With statement. I have used the active worksheet
' but I could have written 'With Worksheets("Sheet2")' or
' made the worksheet name a variable.
With ActiveSheet
' Cells(R,C) identifies a cell within the active worksheet by its row
' and column number.
' .Cells(R,C) identifies a cell within the worksheet named in the With
' statement by its row and column number.
' Rows.Count gives the maximum row number in your version of Excel.
' This statement starts at the bottom of column ColStart, moves up until
' it reaches a cell with a value and returns its row number.
' With this I could write:
' For RowCrnt = RowStart to RowLast
' -----
' Next
' I have kept your style. But I suggest you experiment with Ctrl+Up,
' which is the keyboard equivalent of this VBA and look up "End" in
' VBA help.
RowLast = .Cells(Rows.Count, ColStart).End(xlUp).Row
RowCrnt = RowStart
Do While Not IsEmpty(.Cells(RowCrnt, ColStart).Value)
' This is not necessary; I can use .Cells(RowCrnt, ColStart).Value
' in the file open statment. But this makes it clearer what I am
' doing. When you need to update this macro in six or twelve
' months you will immediately see what the macro is doing.
FileName = .Cells(RowCrnt, ColStart).Value
' Why the extension "MHD"? It is easier to stick to standard
' extensions.
MyFile = PathCrnt & FileName & ".mhd"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
' There are lots of different ways of concatenating the cells in
' a row. I will not claim this is the best but I think it is easy
' to understand. Having it in a loop means it is easy to change
' the number of columns written to the file.
FileLine = .Cells(RowCrnt, 6).Value
For ColCrnt = 7 To 27
FileLine = FileLine & " " & .Cells(RowCrnt, ColCrnt).Value
Next
Print #fnum, FileLine
Close #fnum
RowCrnt = RowCrnt + 1
Loop
End With
End Sub