Solved

stop Visa spreadsheet from looking for a file

Posted on 2013-11-05
12
279 Views
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.
0
Comment
Question by:JaseSt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 29

Expert Comment

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

gowflow
0
 

Author Comment

by:JaseSt
ID: 39624282
buttons
0
 
LVL 29

Expert Comment

by:gowflow
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
gowflow
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

by:gowflow
ID: 39625642
Let me review the code. I have it here but no problem.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 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
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
0
 

Author Closing Comment

by:JaseSt
ID: 39625700
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!
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39625785
ok go ahead
gowflow
0
 

Author Comment

by:JaseSt
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.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39628307
ok no problem
gowflow
0
 

Author Comment

by:JaseSt
ID: 39656327
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!
0
 

Expert Comment

by:mabehr
ID: 40003030
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
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

733 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