在声明两个单独的工作簿时遇到麻烦-当我这样做时得到一个类型不匹配错误

Question

我正在编写代码来记录当前日期并计算单独工作簿中的行数。前一个工作簿是Sales，后一个工作簿称为tracker(文件名)。我想计算销售额中的行数，并将其(连同日期)打印到跟踪器工作簿中。这是我一直在使用的代码:

Sub StoreDate()
Dim SalesWb As Workbook, TrackerWb As Workbook
Set SalesWb = "Sales" & ".xlsm"
Set TrackerWb = "Tracker" & ".xlsm"
Dim SalesWs As Worksheet, TrackerWs As Worksheet
Set SalesWs = SalesWb("Data")
Set TrackerWs = TrackerWb("Tracker")
Dim last_row As Long
Dim Date1 As Long
LDate = Date
last_row = SalesWs.Cells(Rows.Count, 1).End(xlUp).Row
TrackerWs.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = last_row
TrackerWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = LDate
End Sub

Answer 1

对语法进行了一些更正:

Sub StoreDate()

Dim SalesWb As Workbook, TrackerWb As Workbook
Dim SalesWs As Worksheet, TrackerWs As Worksheet
Dim last_row As Long
Dim Date1 As string
'Open Workbooks
Set SalesWb = workbooks.open("Sales" & ".xlsm")
Set TrackerWb = workbooks.open("Tracker" & ".xlsm")
'reference worksheets
Set SalesWs = SalesWb.worksheets("Data")
Set TrackerWs = TrackerWb.worksheets("Tracker")
LDate = Date  'get system data into string
last_row = SalesWs.Cells(Rows.Count, 1).End(xlUp).Row
TrackerWs.Range("B" & Rows.Count).End(xlUp).Offset(1).Value = last_row
TrackerWs.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = LDate
Application.DisplayAlerts = False
SalesWb.close
TrackerWb.close
Application.DisplayAlerts = True

End Sub

Answer 2

复制到另一个工作簿

• 假设以下内容位于(打开的)Tracker.xlsm工作簿中的标准模块(例如Module1)中。如果它位于第三个工作簿中，那么您需要以引用Sales工作簿(...Workbooks.Open(...))的方式引用Tracker工作簿。

Option Explicit
Sub StoreDataReadable()

' Sales: Read the last row into a variable.

Dim swb As Workbook: Set swb = Workbooks.Open("C:TestSales.xlsm")
Dim sws As Worksheet: Set sws = swb.Worksheets("Data")

Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, "A").End(xlUp).Row

swb.Close SaveChanges:=False ' was only read from

' Tracker: Write current date and 'slRow'.

Dim twb As Workbook: Set twb = ThisWorkbook ' workbook containing this code
Dim tws As Worksheet: Set tws = twb.Worksheets("Tracker")

Dim tCell As Range
Set tCell = tws.Cells(tws.Rows.Count, "A").End(xlUp).Offset(1)

tCell.EntireRow.Columns("A").Value = Date ' tCell.Value = Date
tCell.EntireRow.Columns("B").Value = slRow ' tCell.Offset(, 1).Value = slRow

'twb.Save

MsgBox "Data and last row stored.", vbInformation, "Store Data"

End Sub
Sub StoreDataMaintainable()

' Sales
Const sPath As String = "C:TestSales.xlsm"
Const sName As String = "Data"
Const slrCol As String = "A"
' Tracker
Const tName As String = "Tracker"
Const tlrCol As String = "A"
Const tDateCol As String = "A"
Const tRowCol As String = "B"

' Sales: Read the last row into a variable.

Dim swb As Workbook: Set swb = Workbooks.Open(sPath)
Dim sws As Worksheet: Set sws = swb.Worksheets(sName)

Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, slrCol).End(xlUp).Row

swb.Close SaveChanges:=False ' was only read from

' Tracker: Write current date and 'slRow'.

Dim twb As Workbook: Set twb = ThisWorkbook ' workbook containing this code
Dim tws As Worksheet: Set tws = twb.Worksheets(tName)

Dim tCell As Range
Set tCell = tws.Cells(tws.Rows.Count, tlrCol).End(xlUp).Offset(1)

tCell.EntireRow.Columns(tDateCol).Value = Date
tCell.EntireRow.Columns(tRowCol).Value = slRow

'twb.Save

MsgBox "Data and last row stored.", vbInformation, "Store Data"

End Sub