Using File Opened via File Dialog with VBA

Question

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

Add msgbox(sourcename) right before that line. Are you positive you have an open workbook with that exact name? — BruceWayne
– BruceWayne, Commented Aug 31, 2018 at 15:12
When I try it seems like the Window has to be activated. Try changing that line to Windows(fso.GetFileName(xlFileName)).Activate — Kerry Jackson
– Kerry Jackson, Commented Aug 31, 2018 at 15:16
use Workbooks.Open(sourcename), ReadOnly:=True instead of Workbooks(sourcename).Activate — DisplayName
– DisplayName, Commented Aug 31, 2018 at 15:19
also change Dim target As ThisWorkbook to Dim target As Workbook, since Workbook is a type while ThisWorkbook is an object of that class — DisplayName
– DisplayName, Commented Aug 31, 2018 at 15:22
@BruceWayne I added breaks and was able to check that the file name is correct — JustHereChillin
– JustHereChillin, Commented Aug 31, 2018 at 15:29

BruceWayne · Accepted Answer · 2018-08-31 16:05:10Z

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.

answered Aug 31, 2018 at 16:05

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JustHereChillin Over a year ago

I think this might be exactly what I need!

JustHereChillin Over a year ago

I am still receiving error "Object doesn't support this property or method" on the copy/paste line

BruceWayne Over a year ago

@Dee - And you're positive the Workbook and Worksheet references are all accurate, and no typos or anything? Right before the copy/paste line, add these two lines Debug.print mainWB.sheets("DataSource").Range("C1").Value and Debug.print newWB.Worksheets(wksheet).Range("F1").Value. Do you get the values in those two cells, or does one of the lines error?

JustHereChillin Over a year ago

I added both lines and I ran the sub 2 times each time changing the break point between the two lines. Both returned errors.

BruceWayne Over a year ago

@Dee What error(s)? That means there's something incorrect with the mainWB variable and/or the sheet name. Please make positive you're declaring these. Are they indeed correct?

Collectives™ on Stack Overflow

Using File Opened via File Dialog with VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related