vba 从具有特定名称的工作表上的数据创建表格并添加列

Create table from data on a worksheet with specific name and add column

提问by Aeredhal

I have a number of imported worksheets from CSV. The data includes timestamps based on UNIX epoch. Each time I import a new sheet I need to add a column to convert the timestamp to a human readable time.

我有许多从 CSV 导入的工作表。数据包括基于 UNIX 纪元的时间戳。每次导入新工作表时，我都需要添加一列以将时间戳转换为人类可读的时间。

I used record a macro to get started and got this result:

我使用录制宏开始并得到以下结果：

Sub addnamedtable()
'
' addnamedtable Macro
' Takes the imported data, converts it to a table and then adds a column which uses a formula to read the epoch based date stamp
'
    Range("A1:N5614").Select
    ActiveSheet.QueryTables("Temp6").Delete
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$N14"), , xlYes).Name = _
    "Table2"
    Range("Table2[#All]").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=[@[s:timestamp]]/(60*60*24*1000)+""1/1/1970"""
    Range("Table2[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Real_Date"
    Range("O2").Select
End Sub

I then started taking specifics out and replacing them so that it would work with any worksheet and ranges, not just the specific one I had recorded from. This is what I have now.

然后我开始取出细节并替换它们，以便它适用于任何工作表和范围，而不仅仅是我记录的特定工作表。这就是我现在所拥有的。

Sub addnamedtable()
' 
' Takes the imported data, converts it to a table and then adds a column which uses a formula to read the epoch based date stamp
' 

Dim tempname As String
Set tempname = ActiveSheet.Name
    Cells.Select
    ActiveSheet.QueryTables(ActiveSheet.Name).Delete
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$N14"), , xlYes).Name = _tempname
    Range(tempname & "[#All]").Select
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=[@[s:timestamp]]/(60*60*24*1000)+""1/1/1970"""
    Columns("O:O").Select
    Cells.SpecialCells(xlLastCell).Select
    Columns(ActiveCell.Column).Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Range(tempname & "[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Date"
End Sub

I can't get past assigning the sheet name as value to the tempname string. After that I need to name the table based on the sheet to avoid future conflicts in the workbook.

我无法将工作表名称作为值分配给 tempname 字符串。之后，我需要根据工作表命名表以避免工作簿中的未来冲突。

The stuff at the end is probaly nonsense as well but the macro hasn't run through that far to test it.

最后的东西也可能是废话，但宏还没有跑那么远来测试它。

EDIT Following on from the answer by @Head of Catering

编辑继@Head of Catering 的回答之后

Sub addnamedtable()
'
' addnamedtable Macro
'

    Dim tempname As String
    Dim temprange As Range
    tempname = ActiveSheet.Name
    Cells.Select
    Set temprange = Selection
    ActiveSheet.QueryTables(ActiveSheet.Name).Delete
    ActiveSheet.ListObjects.Add(xlSrcRange, Range(temprange), , xlYes).Name = _
    tempname
    Range(tempname & "[#All]").Select
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=[@[s:timestamp]]/(60*60*24*1000)+""1/1/1970"""
    Columns("O:O").Select
    Cells.SpecialCells(xlLastCell).Select
    Columns(ActiveCell.Column).Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Range(tempname & "[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Real_Date"
End Sub

Running this gives me the error

运行这给了我错误

Blockquote Method 'Range' of object '_Global' failed

对象“_Global”的块引用方法“范围”失败

which I think means it doesn't recognise the value of temprangeas a range. I tried temprange.addressbut that caused excel to lock up with no feedback

我认为这意味着它不会将 的值识别temprange为范围。我试过了，temprange.address但这导致 excel 在没有反馈的情况下锁定

采纳答案by Jon Crowell

You don't need the setkeyword unless you are setting an object variable.

set除非您设置对象变量，否则您不需要关键字。

Change

改变

Set tempname = ActiveSheet.Name

to this:

对此：

tempname = ActiveSheet.Name

You have also set temprange equal to all cells in the sheet.

您还将 temprange 设置为等于工作表中的所有单元格。

Change

改变

Cells.Select
Set temprange = Selection

to this, just to get past that error:

对此，只是为了克服那个错误：

' Cells.Select -- comment this out, you don't need it
Set temprange = range("A1:J10")

Edit the range to be the one you actually want.

将范围编辑为您真正想要的范围。

To see what Cells.Select is doing, run this sub and then review the address of the selection in the immediate window.

要查看 Cells.Select 正在做什么，请运行此子程序，然后在即时窗口中查看选择的地址。

Sub CellsSelect()
    Cells.Select
    Debug.Print Selection.Address
End Sub