stop Visa spreadsheet from looking for a file

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.
JaseStAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

gowflow
JaseStAuthor Commented:
buttons
gowflowCommented:
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
gowflow
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

JaseStAuthor Commented:
yes, the folder exists because I ALWAYS have to find the folder and select it EVERY time I open the Visa workbook
gowflowCommented:
Let me review the code. I have it here but no problem.
gowflow
gowflowCommented:
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
Else
    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("L1:L1000").ClearContents
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

WSVisa.Activate
wsMain.Activate
Exit Sub

errHandler:
If Err = 9 Then
    MsgBox ("One of the essential sheets are missing. Pls review and try again")
    End
Else
    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.

gowflow

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JaseStAuthor Commented:
A+

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!
gowflowCommented:
ok go ahead
gowflow
JaseStAuthor Commented:
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.
gowflowCommented:
ok no problem
gowflow
JaseStAuthor Commented:
Finally got to this, gowflow.

Here it is if you can get to it:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28296946.html

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

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28411640.html#a39999893
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.