Encontrei um jeito de ele abrir o arquivo e deu certo. Porém, agora ele abre a planilha, salva a informação na célula P1 a P100 só da primeira planilha, mas não salva nas demais apesar de deixar as linhas delas selecionadas. Então ele salva a informação somente na primeira planilha, mas deixa as demais (nas células p1 até p100) selecionadas e não salva as informações.
Outro fato é que a informação que estou tentando salvar é uma fórmula que traz o nome do arquivo na célula (=CÉL("filename")), porém, ele salva a fórmula mas não executa o resultado. Ela só funciona quando se clica dentro da célula.
Teria como ajustar?
Dim rst As DAO.Recordset, strSQL As String, xls As Object
Dim strlivro$
Set xls = CreateObject("Excel.Application")
strlivro = CurrentProject.Path & "\" & Dir("E:\AGF\teste\" & "*.xl*") 'pasta com o projeto"
xls.Workbooks.Open (strlivro)
xls.Visible = True
xls.Worksheets("bp").Activate ' Nome da planilha
'strSQL = "SELECT * FROM PesoMedio;" 'consulta
strSQL = "SELECT tabela1.campo1 FROM tabela1;" 'criando a consulta
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
xls.ActiveSheet.Range("P1:P100").Select ' em que coluna e a partir de qual celula
xls.ActiveCell.CopyFromRecordset rst
xls.ActiveWorkbook.Save
xls.Application.Quit
Set xls = Nothing
Do While Not strlivro = ""
strlivro = Dir
Loop
End Sub