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, Aug 31 '18 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, Aug 31 '18 at 15:16
use `Workbooks.Open(sourcename), ReadOnly:=True` instead of `Workbooks(sourcename).Activate` — DisplayName, Aug 31 '18 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, Aug 31 '18 at 15:22
@BruceWayne I added breaks and was able to check that the file name is correct — JustHereChillin, Aug 31 '18 at 15:29
I believe you want the part after the `.` also and you are removing it. — Scott Craner, Aug 31 '18 at 15:30
@DisplayName how is that different then "Workbooks.Open (xlFileName), ReadOnly:=True" code I have before activating the workbook — JustHereChillin, Aug 31 '18 at 15:31
that way you open the wanted workbook, which also becomes the _active_ one — DisplayName, Aug 31 '18 at 15:34
Instead of opening the workbook, replace that `Workbooks.Open (xlFileName)...` line with `Dim newWB as Workbook // Set newWB = Workbooks.Open(xlFileName, ReadOnly:=True)` then later, just do `newWB.Worksheets(wksheet).Column("F").Copy`. If I'm understanding your macro correctly, the `Get file name from path` part is superfluous and can be avoided by directly assigning the workbook to a variable. (The `//` means a new line) — BruceWayne, Aug 31 '18 at 15:59
@BruceWayne your proposed solution looked promising but I am still receiving error "Object doesn't support this property or method" on the copy/paste line. I added breaks along the code to see what was triggering the error and that line is when the error occurs. Any other suggestions on this issue? I am seeing this error on other such actions, for example when I copy and paste from one sheet to another. — JustHereChillin, Sep 04 '18 at 12:43

score 1 · Answer 1 · answered Aug 31 '18 at 16:05

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 '18 at 16:05

BruceWayne

22,923
15
65
110

I think this might be exactly what I need! – JustHereChillin Aug 31 '18 at 16:14
I am still receiving error "Object doesn't support this property or method" on the copy/paste line – JustHereChillin Aug 31 '18 at 16:19
@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? – BruceWayne Sep 04 '18 at 15:07
I added both lines and I ran the sub 2 times each time changing the break point between the two lines. Both returned errors. – JustHereChillin Sep 04 '18 at 18:18
@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? – BruceWayne Sep 04 '18 at 18:29

Using File Opened via File Dialog with VBA

1 Answers1