Cartaz da primeira vez, por isso, se houver alguma formatação ou diretrizes que eu não tenha seguido, informe-nos para que possamos corrigi-lo.

Então eu estou basicamente perguntando ao usuário pelo diretório de arquivos do arquivo excel, então eu configurei algumas variáveis ​​(originalmente configuradas em público como variáveis ​​de projeto, já que elas estavam sendo usadas e alteradas em outros lugares). Eu também adicionei as linhas para definir essas variáveis ​​para nada (apenas no caso, eu não acho que isso deve importar). Em seguida, defino essas variáveis ​​para o arquivo do Excel, a pasta de trabalho e as planilhas que desejo acessar.

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

Então eu criei duas variáveis ​​de planilha 'sourceSheets' e 'sourceSheetsSum' diferentes, o código funciona se eu usar 'sourceSheets', mas o erro 1004 ocorre se eu usar 'sourceSheetsSum'. Eu também tentei o código com a variável 'sourceSheet' removida completamente, no caso de que estava substituindo 'sourceSheetSum' por algum motivo.

Estou bastante confiante de que a planilha do Excel e as planilhas existem e estão sendo chamadas corretamente, pois executei um código rápido para percorrer todas as planilhas na pasta de trabalho e gerar os nomes, mostrados abaixo.

For j = 1 To sourceBook.Sheets.Count
Debug.Print (Sheets(j).name)
Next j

Com a saída de depuração de

Medições
Resumo de análise
Configurações de análise

Então, alguém tem alguma idéia do que esse erro pode significar, ou como eu posso descobrir mais sobre o que o erro realmente é?

EDIT: Então eu decidi adicionar um pouco para a listagem dos nomes de folhas, não tenho certeza se vai ajudar em tudo.

For j = 1 To sourceBook.Sheets.Count
    listSheet(j) = Sheets(j).name
Next j    
Debug.Print (listSheet(2))    
Set sourceSheetSum = sourceBook.Sheets(listSheet(2))

A depuração imprime Resumo da Análise, portanto, sei que a planilha existe na pasta de trabalho e não deve haver nenhum problema com um 'erro de digitação' nos nomes.
O código ainda tem o mesmo erro na mesma linha.

deusxmach1na: Eu acho que você queria que eu mudasse

Dim sourceXL As Variant                  
Dim sourceBook As Variant
Dim sourceSheet As Variant
Dim sourceSheetSum As Variant

Set sourceSheet = sourceBook.Sheets("Measurements")

Para

Dim sourceXL As Excel.Application
Dim sourceBook As Excel.Workbook
Dim sourceSheet As Worksheet
Dim sourceSheetSum As Worksheet

Set sourceSheet = sourceBook.Worksheets("Measurements")

Mas isso não muda o erro, eu lembro que eu tinha algo parecido com isso, e então mudei desde que eu li que a variante é como pegar tudo, não na verdade que é sólido em qual variante é.