First time poster, so if there is any formatting, or guidelines I failed to adhere to, please let me know so that I can fix it.
So I am basically asking the user for the file directory of the excel file, then I setup some variables (originally set at public as project variables, since these were being used and changed in other places). I have also added the lines to set these variables to nothing (just in case, I do not think that it should matter). I then set these variables to the excel file, workbook, and sheets that I want to access.
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
So I created two different sheet variables 'sourceSheets' and 'sourceSheetsSum', the code works if i use 'sourceSheets', but error 1004 occurs if i use 'sourceSheetsSum'. I have also tried the code with the variable 'sourceSheet' removed completely, in case that was overriding 'sourceSheetSum' for some reason.
I am fairly confident that the excel workbook and sheets exist and are being called correctly, since I ran a quick bit of code to loop through all the sheets in the workbook and output the names, shown below.
For j = 1 To sourceBook.Sheets.Count
Debug.Print (Sheets(j).name)
Next j
With the debug output of
Measurements
Analysis Summary
Analysis Settings
So, does anyone have any ideas what this error could mean, or how I can possibly go about finding more about what the error actually is?
EDIT: So I decided to add a bit to the listing of the sheet names, not sure if it will help at all.
For j = 1 To sourceBook.Sheets.Count
listSheet(j) = Sheets(j).name
Next j
Debug.Print (listSheet(2))
Set sourceSheetSum = sourceBook.Sheets(listSheet(2))
The debug prints Analysis Summary, so I know that the sheet exists in the workbook, and there should not be any issues with a 'typo' in the names.
The code still has the same error at the same line though.
deusxmach1na: I think you wanted me to change
Dim sourceXL As Variant
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant
Set sourceSheet = sourceBook.Sheets("Measurements")
To
Dim sourceXL As Excel.Application
Dim sourceBook As Excel.Workbook
Dim sourceSheet As Worksheet
Dim sourceSheetSum As Worksheet
Set sourceSheet = sourceBook.Worksheets("Measurements")
But this does not change the error, I remember I had it similar to that, and then changed it since I read that variant is like a catch all, not actually that solid on what variant is.
You have to select the sheet before you can select the range.
I've simplified the example to isolate the problem. Try this:
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
Replace Sheet1 and Sheet2 with your sheet names.
IMPORTANT NOTE: Using Variants is dangerous and can lead to difficult-to-kill bugs. Use them only if you have a very specific reason for doing so.