Access vba excel范围
问题描述:
我正在使用Access来循环访问数据表,并为数据库表中的每一行生成Excel工作簿(包含三张表)。所有的作品都很好,直到我使用“Range”引入代码来隐藏一些列和行。 代码将在第一行成功运行,但会失败。如果我们再次运行代码,它也会失败。如果我们退出Access并重新运行,则第一行再次成功。Access vba excel范围
NewFileName = "C:\Paul2016Puzzle\TestNewName" + "Project" + Str(iteration)
'MsgBox NewFileName
Set XL = New Excel.Application
Set WB = XL.Workbooks.Open(NewFileName)
WB.Activate
Set wks = WB.Worksheets(2)
XL.ScreenUpdating = False
XL.DisplayAlerts = False
wks.Select
WB.Sheets(2).Activate
StrExcel = Chr(65 + WorkingColumns + 1)
StrExcel = StrExcel + ":" + StrExcel
MsgBox StrExcel
WB.Sheets("Sheet 2").Select
WB.Sheets("Sheet 2").Range(StrExcel).Select
WB.Sheets("Sheet 2").Activate
wks.Range(StrExcel).Activate
wks.Columns(StrExcel).Select
wks.Range(StrExcel).Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = True
Rows("12:12").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = True
wks.Cells(1, 1).ColumnWidth = 30 '(Set column width)
For i = 2 To WorkingColumns + 1
wks.Cells(1, i).ColumnWidth = 15
Next i
答
与其尝试创建列字母,只是参考列号。 Chr(65 + WorkingColumns + 1)
将失败 - 如果WorkingColumns是25,它将尝试并参考列[
。
参考您的意见。我用这个方法来找到一个表的最后一个单元格:
' Purpose : Finds the last cell containing data or a formula within the given worksheet.
' If the Optional Col is passed it finds the last row for a specific column.
'---------------------------------------------------------------------------------------
Public Function LastCell(wrkSht As Worksheet, Optional Col As Long = 0) As Range
Dim lLastCol As Long, lLastRow As Long
On Error Resume Next
With wrkSht
If Col = 0 Then
lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lLastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
Else
lLastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
lLastRow = .Columns(Col).Find("*", , , , xlByColumns, xlPrevious).Row
End If
If lLastCol = 0 Then lLastCol = 1
If lLastRow = 0 Then lLastRow = 1
Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
End With
On Error GoTo 0
End Function
然后,您可以用它来找到最后行/列包含数据和隐藏之后,一切:
Public Sub Main()
Dim WB As Workbook
Dim wks As Worksheet
'Dim WorkingColumns As Long
'Dim FirstRow As Long, LastRow As Long
'Dim FirstCol As Long, LastCol As Long
Set WB = ThisWorkbook
Set wks = WB.Worksheets(2)
'Not sure how you get the WorkingColumns figure,
'so have set it to column 5 (column E).
'WorkingColumns = 5
'FirstCol = 2
'LastCol = 8
'FirstRow = 4
'LastRow = 10
'Find the last cell containing data.
Dim rLastCell As Range
Set rLastCell = LastCell(wks)
With wks
'This Offsets by 1 column, so looks at the column after the end of data.
.Range(rLastCell.Offset(, 1), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
.Range(.Cells(13, 1), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
'''''''''''''''''''''''''Second Update'''''''''''''''''''''''
'A range is written as Range(FirstCellRef, LastCellRef).
'Cells references a single cell using row and column numbers (or letters).
'You can use either .Cells(3, 1) or .Cells(3,"A") to reference cell A3.
'.Range(.Cells(1, FirstCol), .Cells(1, LastCol)).EntireColumn.Hidden = True
'.Range(.Cells(FirstRow, 1), .Cells(LastRow, 1)).EntireRow.Hidden = True
'Set width of columns I:L
'.Range(.Cells(1, 9), .Cells(1, 12)).ColumnWidth = 30
'Set width of column N & P (column O is ignored).
'Union(.Cells(1, 14), .Cells(1, 16)).ColumnWidth = 2
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''Original Code'''''''''''''''''''''''
'Resize the number of columns to 8 wide, including column E.
'So E:L.
' .Columns(WorkingColumns).Resize(, 8).Hidden = True
' .Rows(12).Hidden = True
' .Columns(1).ColumnWidth = 30
'Resize Column 2 reference by +4.
'So B:E
' .Columns(2).Resize(, WorkingColumns - 1).ColumnWidth = 15
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End With
End Sub
编辑:已经更新了代码,以使用第一/最后一列和行号而不是Resize方法引用列。
我把这段代码放到了我的程序中,它完美地工作。列数将从一个电子表格到另一个不同,我想隐藏所有列到右侧。行数是不变的,我想隐藏第12行下面的所有行。换句话说,只有电子表格的左上角会显示。谢谢。谢谢。 – RyszardJ
我已经更新了代码,以显示如何隐藏给定第一行和最后一行/列引用的行/列。您可以使用'.Cells(1,1).End(xlToRight)'或任何适合您的方法来获取所需的行/列号。 '.Range(.Cells(1,1),.Cells(1,1).End(xlToRight))。EntireColumn.Hidden = True'也应该有效。 –
谢谢你的帮助。它几乎可以工作,但我不得不为最后一列和最后一行输入常量。但至少我到了那里。最后一件事 - 你可以推荐好的Access手册,因为我只是简单地涉猎和直观地工作。感谢RWJ – RyszardJ