stop Visa spreadsheet from looking for a file

Posted on 2013-11-05
Medium Priority
Last Modified: 2014-04-15
Have my (Visa) spreadsheet that has many functions and when I open it a window opens and asks: "Please choose a Folder location for: Import Monthly Totals file"

This happens every time and I would like it just do this automatically or not ask me to locate the file.
Question by:JaseSt
  • 6
  • 5
LVL 31

Expert Comment

ID: 39624208
Is there anything in the button that is labeled
Choose Location of BlankMonthlyTotals.xls


Author Comment

ID: 39624282
LVL 31

Expert Comment

ID: 39625333
well look at the folder does it really exist on your drive ? maybe you removed it or renamed it ? try pressing on the button and selecting a new folder then save workbook close and reopen and see if the problem persist
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.


Author Comment

ID: 39625359
yes, the folder exists because I ALWAYS have to find the folder and select it EVERY time I open the Visa workbook
LVL 31

Expert Comment

ID: 39625642
Let me review the code. I have it here but no problem.
LVL 31

Accepted Solution

gowflow earned 2000 total points
ID: 39625664
ok lets redo the code here.

1) Make a copy of your latest Visa file and give it a new name.
2) open vba and doubleclcik on sheet Main and click on bottom left icon to view 1 sub at a time.
3) display the code under CommandButton13_Click() delete the code that is there and only keep

Private Sub CommandButton13_Click()

End Sub

4) Copy the below code after Private ...

gstFolderMonthlyTotalsFile = GetNewFolder(gstFolderMonthlyTotalsFile, "Monthly Totals file")
Sheets("Main").CommandButton13.Caption = "Target Export Folder for Monthly Totals File: <" & gstFolderMonthlyTotalsFile & "> ... Activated"

Open in new window

5) SAVE the workbook.
6) dooubleclick on module1 and make sure left bottom icon is clicked and delete Function GetNewFolder

7) Paste the below code after any end sub

Function GetNewFolder(ByVal fFolder As String, Title As String)

fFolder = GFolderName(Title)

If fFolder <> "" And Dir(fFolder, vbDirectory) <> "" Then
    GetNewFolder = fFolder
    MsgBox ("No Folder has been selected or the Folder does not exist, therefore data cannot be Exported" _
        & " until valid Folder has been selected." & Chr(10) & Chr(10) _
        & "Please press on the command bar to choose a Folder.")
    GetNewFolder = "Browse"

    With Application
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    Exit Function
End If
End Function

Open in new window

8) SAVE the workbook.
9) Doubleclik on Thisworkbook in the left pane and delete all the code that is there
10) Paste the below code there

Private Sub Workbook_Deactivate() 'gstGenerateWUName
If gstFolderToMonitor <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderToMonitor", gstFolderToMonitor
If gstFolderToTransfer <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderToTransfer", gstFolderToTransfer
If gstFolderWesternUnion <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderWesternUnion", gstFolderWesternUnion
If gstFolderVisaFile <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderVisaFile", gstFolderVisaFile
If gstFolderMonthlyTotalsFile <> "" Then SaveSetting APP_CATEGORY, APPNAME, "FolderMonthlyTotalsFile", gstFolderMonthlyTotalsFile
If gstGenerateWUName <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateWUName", gstGenerateWUName
If gstGenerateWUEmail <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateWUEmail", gstGenerateWUEmail
If gstGenerateVisaName <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateVisaName", gstGenerateVisaName
If gstGenerateVisaEmail <> "" Then SaveSetting APP_CATEGORY, APPNAME, "GenerateVisaEmail", gstGenerateVisaEmail

End Sub

Private Sub Workbook_Open()
On Error GoTo errHandler

'Application.EnableEvents = True
'Application.DisplayAlerts = True
'Application.ScreenUpdating = True

StartPGM = True
gstFolderToMonitor = GetSetting(APP_CATEGORY, APPNAME, "FolderToMonitor", vbNullString)
gstFolderToTransfer = GetSetting(APP_CATEGORY, APPNAME, "FolderToTransfer", vbNullString)
gstFolderWesternUnion = GetSetting(APP_CATEGORY, APPNAME, "FolderWesternUnion", vbNullString)
gstFolderVisaFile = GetSetting(APP_CATEGORY, APPNAME, "FolderVisaFile", vbNullString)
gstFolderMonthlyTotalsFile = GetSetting(APP_CATEGORY, APPNAME, "FolderMonthlyTotalsFile", vbNullString)
gstGenerateWUName = GetSetting(APP_CATEGORY, APPNAME, "GenerateWUName", vbNullString)
gstGenerateWUEmail = GetSetting(APP_CATEGORY, APPNAME, "GenerateWUEmail", vbNullString)
gstGenerateVisaName = GetSetting(APP_CATEGORY, APPNAME, "GenerateVisaName", vbNullString)
gstGenerateVisaEmail = GetSetting(APP_CATEGORY, APPNAME, "GenerateVisaEmail", vbNullString)

Set WSVisa = ThisWorkbook.Sheets("Wire-Staging-FBME")
Set wsMain = ThisWorkbook.Sheets("Main")

CRow = 1
wsMain.Range("L" & CRow) = "Workbook Open - gstFolderToMonitor: " & gstFolderToMonitor
wsMain.Range("L" & CRow + 1) = "Workbook Open - gstFolderToTransfer: " & gstFolderToTransfer
wsMain.Range("L" & CRow + 2) = "Workbook Open - gstFolderWesternUnion: " & gstFolderWesternUnion
wsMain.Range("L" & CRow + 3) = "Workbook Open - gstFolderVisaFile: " & gstFolderVisaFile
wsMain.Range("L" & CRow + 4) = "Workbook Open - gstFolderMonthlyTotalsFile: " & gstFolderMonthlyTotalsFile
wsMain.Range("L" & CRow + 5) = "Workbook Open - Last GenerateWUName: " & gstGenerateWUName
wsMain.Range("L" & CRow + 6) = "Workbook Open - Last GenerateWUEmail: " & gstGenerateWUEmail
wsMain.Range("L" & CRow + 7) = "Workbook Open - Last GenerateVisaName: " & gstGenerateVisaName
wsMain.Range("L" & CRow + 8) = "Workbook Open - Last GenerateVisaEmail: " & gstGenerateVisaEmail
CRow = CRow + 9

Exit Sub

If Err = 9 Then
    MsgBox ("One of the essential sheets are missing. Pls review and try again")
    MsgBox (Error(Err))
    wsMain.Range("L" & CRow) = "Workbook Open - Error: <" & Error(Err) & ">"
    CRow = CRow + 1

    Resume Next
End If

End Sub

Open in new window

11) SAVE and Exit the workbook
12) Open it and give it a try.


Author Closing Comment

ID: 39625700

Thank you, gowflow. Works perfectly and much smoother to open.

I have another update to a function you worked on a little while ago. Let me know if you're able and willing.

Thanks again!
LVL 31

Expert Comment

ID: 39625785
ok go ahead

Author Comment

ID: 39628300
Will post this later today or tomorrow. A few things have changed so have to make sure I have the change understood by myself first.
LVL 31

Expert Comment

ID: 39628307
ok no problem

Author Comment

ID: 39656327
Finally got to this, gowflow.

Here it is if you can get to it:



Expert Comment

ID: 40003030
hi gowflow, any chance you can work on this one? Should be just a quick fix, I would think:


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question