Solved

stop Visa spreadsheet from looking for a file

Posted on 2013-11-05
12
276 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

785 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