第一次海报,所以如果有任何格式或指导方针我没有遵守,请让我知道,以便我可以解决它。
所以我基本上是要求用户的文件目录的excel文件,然后我设置一些变量(原来设置为公共的项目变量,因为这些被使用和改变在其他地方)。我还添加了将这些变量设置为无关的行(以防万一,我不认为它应该是重要的)。然后我将这些变量设置为要访问的excel文件,工作簿和工作表。
Dim filepath as String filePath = CStr(fileDialog) 'ask file dir,set to string Dim sourceXL As Variant 'these three were orig project variables Dim sourceBook As Variant Dim sourceSheet As Variant Dim sourceSheetSum As Variant Set sourceXL = Nothing 'set to nothing in case...? Set sourceBook = Nothing Set sourceSheet = Nothing Set sourceSheetSum = Nothing Set sourceXL = Excel.Application 'set to the paths needed Set sourceBook = sourceXL.Workbooks.Open(filePath) Set sourceSheet = sourceBook.Sheets("Measurements") Set sourceSheetSum = sourceBook.Sheets("Analysis Summary") Dim measName As Variant 'create variable to access later Dim partName As Variant sourceSheetSum.Range("C3").Select 'THIS IS THE PROBLEM LINE measName = sourceSheetSum.Range(Selection,Selection.End(xlDown)).Value sourceSheetSum.Range("D3").Select partName = sourceSheetSum.Range(Selection,Selection.End(xlDown)).Value
所以我创建了两个不同的表单变量’sourceSheets’和’sourceSheetsSum’,如果我使用’sourceSheets’,代码就可以工作,但是如果我使用’sourceSheetsSum’,就会出现错误1004。我还尝试了完全删除变量’sourceSheet’的代码,以防某些原因覆盖了’sourceSheetSum’。
我相信excel工作簿和工作表存在并被正确调用,因为我运行了一些快速的代码来循环遍历工作簿中的所有工作表,并输出名称,如下所示。
For j = 1 To sourceBook.Sheets.Count Debug.Print (Sheets(j).name) Next j
具有调试输出
Measurements
Analysis Summary
Analysis Settings
那么,有没有人有任何想法这个错误可能意味着什么,或者我可以如何去寻找更多的错误实际上是什么?
编辑:
所以我决定添加一些列表的名单,不知道是否会有所帮助。
For j = 1 To sourceBook.Sheets.Count listSheet(j) = Sheets(j).name Next j Debug.Print (listSheet(2)) Set sourceSheetSum = sourceBook.Sheets(listSheet(2))
调试打印“分析摘要”,因此我知道该工作表存在于工作簿中,名称中不会出现“输入错误”问题。
虽然代码在同一行仍然有相同的错误。
deusxmach1na:我想你想让我改变
Dim sourceXL As Variant Dim sourceBook As Variant Dim sourceSheet As Variant Dim sourceSheetSum As Variant Set sourceSheet = sourceBook.Sheets("Measurements")
至
Dim sourceXL As Excel.Application Dim sourceBook As Excel.Workbook Dim sourceSheet As Worksheet Dim sourceSheetSum As Worksheet Set sourceSheet = sourceBook.Worksheets("Measurements")
但是这并不会改变错误,我记得我有类似的,然后改变它,因为我读到这个变体就像一个catch,而实际上并不是什么变体。
您必须选择工作表才能选择范围。
我已经简化了示例来隔离问题。尝试这个:
Option Explicit Sub RangeError() Dim sourceBook As Workbook Dim sourceSheet As Worksheet Dim sourceSheetSum As Worksheet Set sourceBook = ActiveWorkbook Set sourceSheet = sourceBook.Sheets("Sheet1") Set sourceSheetSum = sourceBook.Sheets("Sheet2") sourceSheetSum.Select sourceSheetSum.Range("C3").Select 'THIS IS THE PROBLEM LINE End Sub
将Sheet1和Sheet2替换为工作表名称。