StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is the code I generally use to force a user to select the workbook that needs to be opened:</p> <pre><code>Dim f As Object, fso As Object, flder As Object Dim myfile As String Dim wb As Workbook, ws As Worksheet Set wb = ActiveWorkbook Set ws = ActiveSheet Set fso = CreateObject("Scripting.FileSystemObject") With Application.FileDialog(msoFileDialogFilePicker) .Show If .SelectedItems.Count = 0 Then MsgBox "Cancel Selected" End End If myfile = .SelectedItems(1) End With Set Wbk= Workbooks.Open(myfile) </code></pre> <p>Alternatively you can store all the file locations in cells on the first sheet, that way they only need to update the spreadsheet with the new file location and not edit the code or select the workbook every time.</p> <p>To integrate this with your code, it would look something like this:</p> <pre><code>'lastCol = Worksheets("Dashboard").Cells(3, Columns.Count).End(xlToLeft).Column lastCol = 15 lastrow = Worksheets("Dashboard").Cells(Rows.Count, 1).End(xlUp).Row Set cmpRng = Range(Cells(1, 1), Cells(3, lastCol)) a = cmpRng i = Cells(Rows.Count, 1).End(xlUp).Row Dim f As Object, fso As Object, flder As Object Dim myfile As String Dim wb As Workbook, ws As Worksheet Set wb = ActiveWorkbook ' this variable lets us go back to our original workbook Set ws = ActiveSheet ' this variable lets us go back to our original sheet Set fso = CreateObject("Scripting.FileSystemObject") With Application.FileDialog(msoFileDialogFilePicker) .Show If .SelectedItems.Count = 0 Then MsgBox "Cancel Selected" End End If myfile = .SelectedItems(1) End With dim wbk as workbook ' use this variable to reference the workbook we're opening dim ghws as worksheet ' this variable should reference the new sheet that gets opened Set Wbk= Workbooks.Open(myfile) set ghws = activesheet ghws.Select Mastcol = Cells(1, Columns.Count).End(xlToLeft).Column j = Cells(Rows.Count, 1).End(xlUp).Row Set mastRng = Range(Cells(1, 1), Cells(1, Mastcol)) b = mastRng For k = 1 To lastCol For n = 1 To Mastcol If UCase(a(3, k)) = UCase(b(1, n)) Then wbk.Activate ghws.Range(Cells(2, n), Cells(j, n)).Copy wbk.Activate Ws.Select Cells(i + 1, k).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Exit For End If Next Next Call Wbk.Close(False) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload