VBA:将单元格内容保存到Mac上特定位置的文本文件 [英] VBA: save cell contents to text file in a specific location on Mac

问题描述

我真的是VBA的新手，我正在尝试编写一个宏，它将某些特定单元的内容保存到Mac上的特定位置.整个代码可以正常工作，只是无法保存到正确的位置；所有文件都保存到桌面.

I'm really new to VBA, and I'm trying write a macro that will save the contents of some specifc cells to a specific location on my Mac. The whole code works fine, EXCEPT that it won't save to the right location; all files save to the desktop.

基本上，A1开始包含这样的内容:"260-CategoryA-555.555.555.555-2012-11-06 17:43:49"，我希望宏保存第A列第2-61行的内容到以单元格A1中前三个数字命名的文本文件.我要保存到的位置取决于单元格A1最初包含文本"CategoryA"还是"CategoryB".同样，它可以将数据导出到文本文件中，但是只能保存到桌面.

Basically, A1 starts out containing something like this "260 - CategoryA - 555.555.555.555 - 2012-11-06 17:43:49," and I want the macro to save the contents of column A, rows 2-61 to a text file named after the first 3 numbers in cell A1. The location I want it to save to depends on whether cell A1 originally contains the text "CategoryA" or "CategoryB". Again, it export the data to a text file just fine, but will only save to the desktop.

任何帮助都会很棒！

Public Sub Columns_2_TextFile()

    Const My_Path1 = "Users:Username:Desktop:Folder1"
    Const My_Path2 = "Users:Username:Desktop:Folder2"
    Dim iCol As Integer
    Dim lRow As Long
    Dim File_Num As Long
    Dim SaveDest As String

    On Error Resume Next
    If InStr(1, Cells(1, 1).Value, "CategoryA", vbTextCompare) > 0 Then
        If Trim(Dir(My_Path1, vbDirectory)) = "" Then
            MkDir My_Path1
        Else
            Kill My_Path1 & "*.txt"
        End If
    ElseIf InStr(1, Cells(1, 1).Value, "CategoryB", vbTextCompare) > 0 Then
        If Trim(Dir(My_Path2, vbDirectory)) = "" Then
            MkDir My_Path2
        Else
            Kill My_Path2 & "*.txt"
        End If
    End If
    On Error GoTo 0
    File_Num = FreeFile
    With ActiveSheet
        Cells(1, 1).Value = Left(Cells(1, 1), 3)
        Open Trim(.Cells(1, 1).Value) & ".txt" For Output As #File_Num
        For lRow = 2 To 61
            Print #File_Num, .Cells(lRow, 1).Value
        Next
        Close #File_Num
    End With

End Sub

推荐答案

我认为您遇到了这个问题，因为您没有指定要在其中输出文件的 Open 文件夹.定义输出文件名和输出文件夹名称的代码.

I think you are having this problem since you are not specifying the folder to Open you output file in. I've modified your code to define an output filename and an output folder name.

注意:您可以使用 Application.PathSeperator 允许通用代码在Mac和Windows上运行.

Note: You can use the Application.PathSeperator to allow common code to run on Mac and Windows.

    Public Sub Columns_2_TextFile()

    Const My_Path1 = "Users:Username:Desktop:Folder1"
    Const My_Path2 = "Users:Username:Desktop:Folder2"
    Dim iCol As Integer
    Dim lRow As Long
    Dim File_Num As Long
    Dim SaveDest As String
    'Define new variables here to hold output filename and output folder
    Dim sOutFolder As String, sOutFile As String

    On Error Resume Next
    If InStr(1, Cells(1, 1).Value, "CategoryA", vbTextCompare) > 0 Then
        'Define the output folder if CategoryA here------------------
        sOutFolder = My_Path1
    ElseIf InStr(1, Cells(1, 1).Value, "CategoryB", vbTextCompare) > 0 Then
        'Define the output folder if CategoryB here-------------------
        sOutFolder = My_Path2
    End If

    'You can also make the code a bit more efficient by taking this out of the other If statement
    If Trim(Dir(My_sOutFolder, vbDirectory)) = "" Then
        MkDir My_sOutFolder
    Else
        Kill My_sOutFolder & "*.txt"
    End If

    On Error GoTo 0
    File_Num = FreeFile
    With ActiveSheet
        'Specify the output filename without destroying the original value
        sOutFile = Left(Cells(1, 1).Value, 3)
        'Specify the correct output folder and the output file name
        Open sOutFolder & Application.PathSeparator & Trim(sOutFile) & ".txt" For Output As #File_Num
        For lRow = 2 To 61
            Print #File_Num, .Cells(lRow, 1).Value
        Next
        Close #File_Num
    End With

End Sub

这篇关于VBA:将单元格内容保存到Mac上特定位置的文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！