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
Solved

stop Visa spreadsheet from looking for a file

Posted on 2013-11-05
12
278 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA modules import 4 56
Need Help with Mapping ONLY 9 24
Adding Additional Criteria to a formula 17 45
Need conditional formating when doubles 14 16
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

839 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