DEVHIDE
Login Or Sign up

VBA Prompting "Open" dialog box and choosing directory to import import from

83 Views Asked by At

i have this VBA-code i need some help with. The purpose of it is to generate a word-report based on parameters from an excel-document, thus far everything is fine. The problem is i want to distribute the same templates to colleagues without them having to change the code. In its' current form, it opens an excel document from a directory specified in the code. I want to change this to retrieving the information from an excel document chosen by the user. I've read about the GetOpenFilename-method which should prompt the dialog box "Open", but have not managed to get it to work. Appreciate any suggestions. 

Private Sub Document_Open()

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("C:\Users\Document.xlsx")

ThisDocument.number_name.Caption = exWb.Sheets("Reporting").Cells(4, 2)
ThisDocument.period.Caption = exWb.Sheets("Reporting").Cells(3, 2)

ThisDocument.nr_persons.Caption = exWb.Sheets("Reporting").Cells(6, 2)
ThisDocument.total_people.Caption = exWb.Sheets("Reporting").Cells(6, 3)
ThisDocument.nr_subcontracts.Caption = exWb.Sheets("Reporting").Cells(7, 2)
ThisDocument.thrid_party.Caption = exWb.Sheets("Reporting").Cells(8, 2)
ThisDocument.travel_costs.Caption = exWb.Sheets("Reporting").Cells(9, 2)
ThisDocument.depreciation.Caption = exWb.Sheets("Reporting").Cells(10, 2)
ThisDocument.other_costs.Caption = exWb.Sheets("Reporting").Cells(11, 2)

ThisDocument.res23sec.Caption = exWb.Sheets("Reporting").Cells(37, 3)
ThisDocument.res29sec.Caption = exWb.Sheets("Reporting").Cells(43, 3)
ThisDocument.res33sec.Caption = exWb.Sheets("Reporting").Cells(47, 3)
ThisDocument.res33ter.Caption = exWb.Sheets("Reporting").Cells(47, 4)

ThisDocument.res1.Caption = exWb.Sheets("Reporting").Cells(15, 2)
ThisDocument.res2.Caption = exWb.Sheets("Reporting").Cells(16, 2)
ThisDocument.res3.Caption = exWb.Sheets("Reporting").Cells(17, 2)
ThisDocument.res4.Caption = exWb.Sheets("Reporting").Cells(18, 2)
ThisDocument.res5.Caption = exWb.Sheets("Reporting").Cells(19, 2)
ThisDocument.res6.Caption = exWb.Sheets("Reporting").Cells(20, 2)
ThisDocument.res7.Caption = exWb.Sheets("Reporting").Cells(21, 2)
ThisDocument.res8.Caption = exWb.Sheets("Reporting").Cells(22, 2)
ThisDocument.res9.Caption = exWb.Sheets("Reporting").Cells(23, 2)
ThisDocument.res10.Caption = exWb.Sheets("Reporting").Cells(24, 2)
ThisDocument.res11.Caption = exWb.Sheets("Reporting").Cells(25, 2)
ThisDocument.res12.Caption = exWb.Sheets("Reporting").Cells(26, 2)
ThisDocument.res13.Caption = exWb.Sheets("Reporting").Cells(27, 2)
ThisDocument.res14.Caption = exWb.Sheets("Reporting").Cells(28, 2)
ThisDocument.res15.Caption = exWb.Sheets("Reporting").Cells(29, 2)
ThisDocument.res16.Caption = exWb.Sheets("Reporting").Cells(30, 2)
ThisDocument.res17.Caption = exWb.Sheets("Reporting").Cells(31, 2)
ThisDocument.res18.Caption = exWb.Sheets("Reporting").Cells(32, 2)
ThisDocument.res19.Caption = exWb.Sheets("Reporting").Cells(33, 2)
ThisDocument.res20.Caption = exWb.Sheets("Reporting").Cells(34, 2)
ThisDocument.res21.Caption = exWb.Sheets("Reporting").Cells(35, 2)
ThisDocument.res22.Caption = exWb.Sheets("Reporting").Cells(36, 2)
ThisDocument.res23.Caption = exWb.Sheets("Reporting").Cells(37, 2)
ThisDocument.res24.Caption = exWb.Sheets("Reporting").Cells(38, 2)
ThisDocument.res25.Caption = exWb.Sheets("Reporting").Cells(39, 2)
ThisDocument.res26.Caption = exWb.Sheets("Reporting").Cells(40, 2)
ThisDocument.res27.Caption = exWb.Sheets("Reporting").Cells(41, 2)
ThisDocument.res28.Caption = exWb.Sheets("Reporting").Cells(42, 2)
ThisDocument.res29.Caption = exWb.Sheets("Reporting").Cells(43, 2)
ThisDocument.res30.Caption = exWb.Sheets("Reporting").Cells(44, 2)
ThisDocument.res31.Caption = exWb.Sheets("Reporting").Cells(45, 2)
ThisDocument.res32.Caption = exWb.Sheets("Reporting").Cells(46, 2)
ThisDocument.res33.Caption = exWb.Sheets("Reporting").Cells(47, 2)
ThisDocument.res34.Caption = exWb.Sheets("Reporting").Cells(48, 2)
ThisDocument.res35.Caption = exWb.Sheets("Reporting").Cells(49, 2)
ThisDocument.res36.Caption = exWb.Sheets("Reporting").Cells(50, 2)
ThisDocument.res37.Caption = exWb.Sheets("Reporting").Cells(51, 2)
ThisDocument.res38.Caption = exWb.Sheets("Reporting").Cells(52, 2)
ThisDocument.res39.Caption = exWb.Sheets("Reporting").Cells(53, 2)
ThisDocument.res40.Caption = exWb.Sheets("Reporting").Cells(54, 2)
ThisDocument.res41.Caption = exWb.Sheets("Reporting").Cells(55, 2)
ThisDocument.res42.Caption = exWb.Sheets("Reporting").Cells(56, 2)
ThisDocument.res43.Caption = exWb.Sheets("Reporting").Cells(57, 2)
ThisDocument.res44.Caption = exWb.Sheets("Reporting").Cells(58, 2)
ThisDocument.res45.Caption = exWb.Sheets("Reporting").Cells(59, 2)
ThisDocument.res46.Caption = exWb.Sheets("Reporting").Cells(60, 2)
ThisDocument.res47.Caption = exWb.Sheets("Reporting").Cells(61, 2)
ThisDocument.res48.Caption = exWb.Sheets("Reporting").Cells(62, 2)
ThisDocument.res49.Caption = exWb.Sheets("Reporting").Cells(63, 2)
ThisDocument.res50.Caption = exWb.Sheets("Reporting").Cells(64, 2)
ThisDocument.res51.Caption = exWb.Sheets("Reporting").Cells(65, 2)
ThisDocument.res52.Caption = exWb.Sheets("Reporting").Cells(66, 2)
ThisDocument.res53.Caption = exWb.Sheets("Reporting").Cells(67, 2)
ThisDocument.res54.Caption = exWb.Sheets("Reporting").Cells(68, 2)
ThisDocument.res55.Caption = exWb.Sheets("Reporting").Cells(69, 2)
ThisDocument.res56.Caption = exWb.Sheets("Reporting").Cells(70, 2)
ThisDocument.res57.Caption = exWb.Sheets("Reporting").Cells(71, 2)
ThisDocument.res58.Caption = exWb.Sheets("Reporting").Cells(72, 2)
ThisDocument.res59.Caption = exWb.Sheets("Reporting").Cells(73, 2)
ThisDocument.res60.Caption = exWb.Sheets("Reporting").Cells(74, 2)
ThisDocument.res61.Caption = exWb.Sheets("Reporting").Cells(75, 2)
ThisDocument.res62.Caption = exWb.Sheets("Reporting").Cells(76, 2)
ThisDocument.res63.Caption = exWb.Sheets("Reporting").Cells(77, 2)

exWb.Close

Set exWb = Nothing

End Sub
excel vba getopenfilename
Original Q&A
2

There are 2 best solutions below

0
On BEST ANSWER

I had success with the fileDialog property. You might be able to use it here:

Private Sub Document_Open()

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim selectedItem as string

With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show
 selecteditem = .SelectedItems(1)
 End With
'Workbooks.Open Filename:=selecteditem
Set exWb = objExcel.Workbooks.Open(selecteditem)

ThisDocument.number_name.Caption = exWb.Sheets("Reporting").Cells(4, 2)
ThisDocument.period.Caption = exWb.Sheets("Reporting").Cells(3, 2)

ThisDocument.nr_persons.Caption = exWb.Sheets("Reporting").Cells(6, 2)
ThisDocument.total_people.Caption = exWb.Sheets("Reporting").Cells(6, 3)
ThisDocument.nr_subcontracts.Caption = exWb.Sheets("Reporting").Cells(7, 2)
ThisDocument.thrid_party.Caption = exWb.Sheets("Reporting").Cells(8, 2)
ThisDocument.travel_costs.Caption = exWb.Sheets("Reporting").Cells(9, 2)
ThisDocument.depreciation.Caption = exWb.Sheets("Reporting").Cells(10, 2)
ThisDocument.other_costs.Caption = exWb.Sheets("Reporting").Cells(11, 2)

ThisDocument.res23sec.Caption = exWb.Sheets("Reporting").Cells(37, 3)
ThisDocument.res29sec.Caption = exWb.Sheets("Reporting").Cells(43, 3)
ThisDocument.res33sec.Caption = exWb.Sheets("Reporting").Cells(47, 3)
ThisDocument.res33ter.Caption = exWb.Sheets("Reporting").Cells(47, 4)

ThisDocument.res1.Caption = exWb.Sheets("Reporting").Cells(15, 2)
ThisDocument.res2.Caption = exWb.Sheets("Reporting").Cells(16, 2)
ThisDocument.res3.Caption = exWb.Sheets("Reporting").Cells(17, 2)
ThisDocument.res4.Caption = exWb.Sheets("Reporting").Cells(18, 2)
ThisDocument.res5.Caption = exWb.Sheets("Reporting").Cells(19, 2)
ThisDocument.res6.Caption = exWb.Sheets("Reporting").Cells(20, 2)
ThisDocument.res7.Caption = exWb.Sheets("Reporting").Cells(21, 2)
ThisDocument.res8.Caption = exWb.Sheets("Reporting").Cells(22, 2)
ThisDocument.res9.Caption = exWb.Sheets("Reporting").Cells(23, 2)
ThisDocument.res10.Caption = exWb.Sheets("Reporting").Cells(24, 2)
ThisDocument.res11.Caption = exWb.Sheets("Reporting").Cells(25, 2)
ThisDocument.res12.Caption = exWb.Sheets("Reporting").Cells(26, 2)
ThisDocument.res13.Caption = exWb.Sheets("Reporting").Cells(27, 2)
ThisDocument.res14.Caption = exWb.Sheets("Reporting").Cells(28, 2)
ThisDocument.res15.Caption = exWb.Sheets("Reporting").Cells(29, 2)
ThisDocument.res16.Caption = exWb.Sheets("Reporting").Cells(30, 2)
ThisDocument.res17.Caption = exWb.Sheets("Reporting").Cells(31, 2)
ThisDocument.res18.Caption = exWb.Sheets("Reporting").Cells(32, 2)
ThisDocument.res19.Caption = exWb.Sheets("Reporting").Cells(33, 2)
ThisDocument.res20.Caption = exWb.Sheets("Reporting").Cells(34, 2)
ThisDocument.res21.Caption = exWb.Sheets("Reporting").Cells(35, 2)
ThisDocument.res22.Caption = exWb.Sheets("Reporting").Cells(36, 2)
ThisDocument.res23.Caption = exWb.Sheets("Reporting").Cells(37, 2)
ThisDocument.res24.Caption = exWb.Sheets("Reporting").Cells(38, 2)
ThisDocument.res25.Caption = exWb.Sheets("Reporting").Cells(39, 2)
ThisDocument.res26.Caption = exWb.Sheets("Reporting").Cells(40, 2)
ThisDocument.res27.Caption = exWb.Sheets("Reporting").Cells(41, 2)
ThisDocument.res28.Caption = exWb.Sheets("Reporting").Cells(42, 2)
ThisDocument.res29.Caption = exWb.Sheets("Reporting").Cells(43, 2)
ThisDocument.res30.Caption = exWb.Sheets("Reporting").Cells(44, 2)
ThisDocument.res31.Caption = exWb.Sheets("Reporting").Cells(45, 2)
ThisDocument.res32.Caption = exWb.Sheets("Reporting").Cells(46, 2)
ThisDocument.res33.Caption = exWb.Sheets("Reporting").Cells(47, 2)
ThisDocument.res34.Caption = exWb.Sheets("Reporting").Cells(48, 2)
ThisDocument.res35.Caption = exWb.Sheets("Reporting").Cells(49, 2)
ThisDocument.res36.Caption = exWb.Sheets("Reporting").Cells(50, 2)
ThisDocument.res37.Caption = exWb.Sheets("Reporting").Cells(51, 2)
ThisDocument.res38.Caption = exWb.Sheets("Reporting").Cells(52, 2)
ThisDocument.res39.Caption = exWb.Sheets("Reporting").Cells(53, 2)
ThisDocument.res40.Caption = exWb.Sheets("Reporting").Cells(54, 2)
ThisDocument.res41.Caption = exWb.Sheets("Reporting").Cells(55, 2)
ThisDocument.res42.Caption = exWb.Sheets("Reporting").Cells(56, 2)
ThisDocument.res43.Caption = exWb.Sheets("Reporting").Cells(57, 2)
ThisDocument.res44.Caption = exWb.Sheets("Reporting").Cells(58, 2)
ThisDocument.res45.Caption = exWb.Sheets("Reporting").Cells(59, 2)
ThisDocument.res46.Caption = exWb.Sheets("Reporting").Cells(60, 2)
ThisDocument.res47.Caption = exWb.Sheets("Reporting").Cells(61, 2)
ThisDocument.res48.Caption = exWb.Sheets("Reporting").Cells(62, 2)
ThisDocument.res49.Caption = exWb.Sheets("Reporting").Cells(63, 2)
ThisDocument.res50.Caption = exWb.Sheets("Reporting").Cells(64, 2)
ThisDocument.res51.Caption = exWb.Sheets("Reporting").Cells(65, 2)
ThisDocument.res52.Caption = exWb.Sheets("Reporting").Cells(66, 2)
ThisDocument.res53.Caption = exWb.Sheets("Reporting").Cells(67, 2)
ThisDocument.res54.Caption = exWb.Sheets("Reporting").Cells(68, 2)
ThisDocument.res55.Caption = exWb.Sheets("Reporting").Cells(69, 2)
ThisDocument.res56.Caption = exWb.Sheets("Reporting").Cells(70, 2)
ThisDocument.res57.Caption = exWb.Sheets("Reporting").Cells(71, 2)
ThisDocument.res58.Caption = exWb.Sheets("Reporting").Cells(72, 2)
ThisDocument.res59.Caption = exWb.Sheets("Reporting").Cells(73, 2)
ThisDocument.res60.Caption = exWb.Sheets("Reporting").Cells(74, 2)
ThisDocument.res61.Caption = exWb.Sheets("Reporting").Cells(75, 2)
ThisDocument.res62.Caption = exWb.Sheets("Reporting").Cells(76, 2)
ThisDocument.res63.Caption = exWb.Sheets("Reporting").Cells(77, 2)

exWb.Close

Set exWb = Nothing

End Sub
0
On

The approach below uses the GetOpenFilename method, and it includes some basic validations to:

  1. Check if the user cancelled the file dialog window instead of selecting a file.
  2. Check if the selected file is indeed an XLSX file.
  3. Check to make sure that the required worksheet named "Reporting" exists in the selected file.
  4. Otherwise, exit the sub.

You could add more file filters or remove the existing one, and do the same for the file extension check. You could remove the code that checks for the required worksheet, or revise the code to check for additional required worksheets.

Private Sub Document_Open()
    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook
    Dim Filepath As Variant
    Dim ws As Excel.Worksheet
    Dim RequiredWorksheetsExist As Boolean

    Filepath = Application.GetOpenFilename(FileFilter:="Excel File (*.xlsx), *.xlsx", FilterIndex:=1, Title:="Open Excel File", MultiSelect:=False)
    ' Check if the user cancelled the open file dialog window.
    If (VarType(Filepath) <> VbVarType.vbString) Then GoTo ExitSub
    ' Check if the selected file has the expected file extension(s).
    If Not (Filepath Like "*.xlsx") Then GoTo ExitSub

    Set exWb = Application.Workbooks.Open(Filename:=Filepath)
    ' Check if expected worksheets exist in the selected file.
    For Each ws In exWb.Worksheets
        If (ws.Name = "Reporting") Then RequiredWorksheetsExist = True
    Next
    If Not (RequiredWorksheetsExist) Then GoTo ExitSub

    ThisDocument.number_name.Caption = exWb.Sheets("Reporting").Cells(4, 2)
    ThisDocument.Period.Caption = exWb.Sheets("Reporting").Cells(3, 2)

    ThisDocument.nr_persons.Caption = exWb.Sheets("Reporting").Cells(6, 2)
    ThisDocument.total_people.Caption = exWb.Sheets("Reporting").Cells(6, 3)
    ThisDocument.nr_subcontracts.Caption = exWb.Sheets("Reporting").Cells(7, 2)
    ThisDocument.thrid_party.Caption = exWb.Sheets("Reporting").Cells(8, 2)
    ThisDocument.travel_costs.Caption = exWb.Sheets("Reporting").Cells(9, 2)
    ThisDocument.depreciation.Caption = exWb.Sheets("Reporting").Cells(10, 2)
    ThisDocument.other_costs.Caption = exWb.Sheets("Reporting").Cells(11, 2)

    ThisDocument.res23sec.Caption = exWb.Sheets("Reporting").Cells(37, 3)
    ThisDocument.res29sec.Caption = exWb.Sheets("Reporting").Cells(43, 3)
    ThisDocument.res33sec.Caption = exWb.Sheets("Reporting").Cells(47, 3)
    ThisDocument.res33ter.Caption = exWb.Sheets("Reporting").Cells(47, 4)

    ThisDocument.res1.Caption = exWb.Sheets("Reporting").Cells(15, 2)
    ThisDocument.res2.Caption = exWb.Sheets("Reporting").Cells(16, 2)
    ThisDocument.res3.Caption = exWb.Sheets("Reporting").Cells(17, 2)
    ThisDocument.res4.Caption = exWb.Sheets("Reporting").Cells(18, 2)
    ThisDocument.res5.Caption = exWb.Sheets("Reporting").Cells(19, 2)
    ThisDocument.res6.Caption = exWb.Sheets("Reporting").Cells(20, 2)
    ThisDocument.res7.Caption = exWb.Sheets("Reporting").Cells(21, 2)
    ThisDocument.res8.Caption = exWb.Sheets("Reporting").Cells(22, 2)
    ThisDocument.res9.Caption = exWb.Sheets("Reporting").Cells(23, 2)
    ThisDocument.res10.Caption = exWb.Sheets("Reporting").Cells(24, 2)
    ThisDocument.res11.Caption = exWb.Sheets("Reporting").Cells(25, 2)
    ThisDocument.res12.Caption = exWb.Sheets("Reporting").Cells(26, 2)
    ThisDocument.res13.Caption = exWb.Sheets("Reporting").Cells(27, 2)
    ThisDocument.res14.Caption = exWb.Sheets("Reporting").Cells(28, 2)
    ThisDocument.res15.Caption = exWb.Sheets("Reporting").Cells(29, 2)
    ThisDocument.res16.Caption = exWb.Sheets("Reporting").Cells(30, 2)
    ThisDocument.res17.Caption = exWb.Sheets("Reporting").Cells(31, 2)
    ThisDocument.res18.Caption = exWb.Sheets("Reporting").Cells(32, 2)
    ThisDocument.res19.Caption = exWb.Sheets("Reporting").Cells(33, 2)
    ThisDocument.res20.Caption = exWb.Sheets("Reporting").Cells(34, 2)
    ThisDocument.res21.Caption = exWb.Sheets("Reporting").Cells(35, 2)
    ThisDocument.res22.Caption = exWb.Sheets("Reporting").Cells(36, 2)
    ThisDocument.res23.Caption = exWb.Sheets("Reporting").Cells(37, 2)
    ThisDocument.res24.Caption = exWb.Sheets("Reporting").Cells(38, 2)
    ThisDocument.res25.Caption = exWb.Sheets("Reporting").Cells(39, 2)
    ThisDocument.res26.Caption = exWb.Sheets("Reporting").Cells(40, 2)
    ThisDocument.res27.Caption = exWb.Sheets("Reporting").Cells(41, 2)
    ThisDocument.res28.Caption = exWb.Sheets("Reporting").Cells(42, 2)
    ThisDocument.res29.Caption = exWb.Sheets("Reporting").Cells(43, 2)
    ThisDocument.res30.Caption = exWb.Sheets("Reporting").Cells(44, 2)
    ThisDocument.res31.Caption = exWb.Sheets("Reporting").Cells(45, 2)
    ThisDocument.res32.Caption = exWb.Sheets("Reporting").Cells(46, 2)
    ThisDocument.res33.Caption = exWb.Sheets("Reporting").Cells(47, 2)
    ThisDocument.res34.Caption = exWb.Sheets("Reporting").Cells(48, 2)
    ThisDocument.res35.Caption = exWb.Sheets("Reporting").Cells(49, 2)
    ThisDocument.res36.Caption = exWb.Sheets("Reporting").Cells(50, 2)
    ThisDocument.res37.Caption = exWb.Sheets("Reporting").Cells(51, 2)
    ThisDocument.res38.Caption = exWb.Sheets("Reporting").Cells(52, 2)
    ThisDocument.res39.Caption = exWb.Sheets("Reporting").Cells(53, 2)
    ThisDocument.res40.Caption = exWb.Sheets("Reporting").Cells(54, 2)
    ThisDocument.res41.Caption = exWb.Sheets("Reporting").Cells(55, 2)
    ThisDocument.res42.Caption = exWb.Sheets("Reporting").Cells(56, 2)
    ThisDocument.res43.Caption = exWb.Sheets("Reporting").Cells(57, 2)
    ThisDocument.res44.Caption = exWb.Sheets("Reporting").Cells(58, 2)
    ThisDocument.res45.Caption = exWb.Sheets("Reporting").Cells(59, 2)
    ThisDocument.res46.Caption = exWb.Sheets("Reporting").Cells(60, 2)
    ThisDocument.res47.Caption = exWb.Sheets("Reporting").Cells(61, 2)
    ThisDocument.res48.Caption = exWb.Sheets("Reporting").Cells(62, 2)
    ThisDocument.res49.Caption = exWb.Sheets("Reporting").Cells(63, 2)
    ThisDocument.res50.Caption = exWb.Sheets("Reporting").Cells(64, 2)
    ThisDocument.res51.Caption = exWb.Sheets("Reporting").Cells(65, 2)
    ThisDocument.res52.Caption = exWb.Sheets("Reporting").Cells(66, 2)
    ThisDocument.res53.Caption = exWb.Sheets("Reporting").Cells(67, 2)
    ThisDocument.res54.Caption = exWb.Sheets("Reporting").Cells(68, 2)
    ThisDocument.res55.Caption = exWb.Sheets("Reporting").Cells(69, 2)
    ThisDocument.res56.Caption = exWb.Sheets("Reporting").Cells(70, 2)
    ThisDocument.res57.Caption = exWb.Sheets("Reporting").Cells(71, 2)
    ThisDocument.res58.Caption = exWb.Sheets("Reporting").Cells(72, 2)
    ThisDocument.res59.Caption = exWb.Sheets("Reporting").Cells(73, 2)
    ThisDocument.res60.Caption = exWb.Sheets("Reporting").Cells(74, 2)
    ThisDocument.res61.Caption = exWb.Sheets("Reporting").Cells(75, 2)
    ThisDocument.res62.Caption = exWb.Sheets("Reporting").Cells(76, 2)
    ThisDocument.res63.Caption = exWb.Sheets("Reporting").Cells(77, 2)

ExitSub:
    If Not (exWb Is Nothing) Then
        exWb.Close SaveChanges:=False
        Set exWb = Nothing
    End If
End Sub

Related Questions in EXCEL

Related Questions in VBA

Related Questions in GETOPENFILENAME

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Popular Questions

Copyright © 2021 Jogjafile Inc.