Using File Opened via File Dialog with VBA

I am trying to Copy information from a tab in file opened via File Dialog and paste it into "ThisWorkbook"

Below is my attempt. I keep getting the error

"object doesn't support this property or method"

on the line in bold font.

Sub UpdateWeeklyJobPrep()
Dim xlFileName As String
Dim fd As Office.FileDialog
Dim source As Workbook
Dim currentwk As Integer
Dim wksheet As String
Dim target As ThisWorkbook
Dim fso As Object
Dim sourcename As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Calc the current fiscal week
currentwk = WorksheetFunction.WeekNum(Now, vbMonday)
wksheet = "FW" & currentwk

With fd
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1

If .Show Then
xlFileName = .SelectedItems(1)
Else
Exit Sub
End If

End With

'Opens workbook
Workbooks.Open (xlFileName), ReadOnly:=True

'Get file name from path
Set fso = CreateObject("Scripting.FileSystemObject")
sourcename = fso.GetFileName(xlFileName)
sourcename = Left(sourcename, InStrRev(sourcename, ".") - 1)

'Copy/Paste Code Here
**Workbooks(sourcename).Activate**
Workbooks(sourcename).Worksheets(wksheet).Column("F").Copy
target.Activate
target.Sheets("Data Source").Column("C").PasteSpecial

'close workbook with saving changes
source.Close SaveChanges:=False
Set source = Nothing


End Sub


msgbox(sourcename)

Windows(fso.GetFileName(xlFileName)).Activate

Workbooks.Open(sourcename), ReadOnly:=True

Workbooks(sourcename).Activate

Dim target As ThisWorkbook

Dim target As Workbook

Workbook

ThisWorkbook

1 Answer
1

I think I have a solution. Primarily, as mentioned above in my comment, you should use a variable to hold your new, open workbook.

Sub UpdateWeeklyJobPrep()
Dim xlFileName As String
Dim fd As Office.FileDialog
Dim source As Workbook
Dim currentwk As Integer
Dim wksheet As String
Dim fso As Object
Dim sourcename As String

Dim mainWB As Workbook

Set mainWB = ThisWorkbook

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Calc the current fiscal week
currentwk = WorksheetFunction.WeekNum(Now, vbMonday)
wksheet = "FW" & currentwk

With fd
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
If .Show Then
xlFileName = .SelectedItems(1)
Else
Exit Sub
End If
End With

'Opens workbook
Dim newWB As Workbook
Set newWB = Workbooks.Open(xlFileName, ReadOnly:=True)

'Copy/Paste Code Here
mainWB.Sheets("Data Source").Column("C").Values = newWB.Worksheets(wksheet).Column("F").Values
newWB.Close savechanges:=False
Set newWB = Nothing
End Sub


I also changed the Copy/PasteSpecial bit, assuming you just needed values. Note since you're copying a whole column this might take time. You'd probably instead want to minimize that range to the used rows only, but I'll leave that as an exercise for the reader.

Copy/PasteSpecial

Debug.print mainWB.sheets("DataSource").Range("C1").Value

Debug.print newWB.Worksheets(wksheet).Range("F1").Value

mainWB

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.