Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-14-2019, 10:36 AM
DaveP DaveP is offline Input Box Help/Storing Variables in other Macros Windows 10 Input Box Help/Storing Variables in other Macros Office 2010
Novice
Input Box Help/Storing Variables in other Macros
 
Join Date: May 2019
Posts: 11
DaveP is on a distinguished road
Default Input Box Help/Storing Variables in other Macros

Hello,


(start at the ** if you don't care about context)

I wrote a program that uses userforms to open up documents from our file explorer and network, as well as run simple macros (mostly formatting). The goal is to save time; we open 5 or 6 different documents on every case, and do ~20 cases per day, so having a userform that offers you those documents, rather than dig through 6 different folders, is a huge deal (believe, me I understand how inefficient that all sounds, but if you knew what we do, it makes sense, I promise, ha).

I'm trying to build a macro that will open a document that has not been created yet. Let's call it AutoText. The department that creates AutoText has not yet made the doc, but I don't want to have to update the user's copy of the program every time we write a new AutoText document, if that makes sense.

*********Basically, I want an input box where you paste a file location, and what you pasted gets added to a macro that opens that file location, and stores the file location for future use.

Sub Send()
InputBox ("File Location: click the files tab; underneath the document title, click the file path, then click 'Copy Path to Clipboard'")
'What goes here? How do I paste to "Sub Receive"?
End Sub

Sub Receive()
'Auto Texts file opener
Shell "Explorer.exe ***the file location entered in the Sub Send input box***", vbNormalFocus
End Sub


So the user would input

C:\Users\davep\Desktop\Files\AutoText.docx

and press enter, and then Sub Receive would now say:

Sub Receive()
'Auto Texts
Shell "Explorer.exe C:\Users\davep\Desktop\File\AutoText.docx", vbNormalFocus
End Sub


Now, in the userform, I have a command button:

Private Sub CommandButton4_Click()
Call Receive
Me.hide
End Sub

So after inputting the file location in Sub Send, Sub Receive now opens AutoText.docx, and when you click CommandButton4, Sub Receive runs, opening AutoText.docx.

TL;DR:
I have an Input box, I want the user to add a file location, it stores the file location to a macro, with a shell command that opens that file location--- that macro is called upon by a command button attached to a userform. How do I store an input box answer inside of an existing macro?
Reply With Quote
  #2  
Old 06-14-2019, 04:12 PM
gmaxey gmaxey is offline Input Box Help/Storing Variables in other Macros Windows 10 Input Box Help/Storing Variables in other Macros Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,421
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default

Your current receive does not open a Word document. Not here at least.


Try


Code:
Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" _
       Alias "ShellExecuteA" (ByVal hwnd As Long, _
                              ByVal lpOperation As String, _
                              ByVal lpFile As String, _
                              ByVal lpParameters As String, _
                              ByVal lpDirectory As String, _
                              ByVal nShowCmd As Long) As Long
Sub Commandbutton()
  Receive InputBox("Enter the file path", "Open Document")
lbl_Exit:
  Exit Sub
End Sub

Public Function Receive(ByVal WhichFilePath As String, _
                               Optional sParams As String = "", _
                               Optional sStartIn As String = vbNullString, _
                               Optional lngOpenMode As Long = 1) As Long
  Receive = ShellExecute(0, vbNullString, WhichFilePath, sParams, sStartIn, lngOpenMode)
lbl_Exit:
  Exit Function
End Function
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote
  #3  
Old 06-18-2019, 01:34 PM
DaveP DaveP is offline Input Box Help/Storing Variables in other Macros Windows 10 Input Box Help/Storing Variables in other Macros Office 2010
Novice
Input Box Help/Storing Variables in other Macros
 
Join Date: May 2019
Posts: 11
DaveP is on a distinguished road
Default

Thanks for the response!

I tried this code out, and it opened a file path, but if it's storing the file path in the macro for future use, I'm not seeing it.

The whole goal is to be able to provide access to templates that have not been created yet, as they are created. On the files tab in word, you can click "copy path to clipboard".
My idea is--- the user will be notified that the new file has been created, they locate the file, run the macro, copy path into the input box, and now forever, instead of doing that process or searching for the correct file, there is a button in the application that has stored the file path to be opened for future use. I hope that makes sense. Our filing system is kind of incredibly specific (necessarily specific, though) and, at times, very hard to navigate. If the user has the option to open a document without ever leaving the document they're working on, it will save us loads of time and frustration.


Was I supposed to run this code with my original code in it somewhere, maybe? I created a module to test this idea in within word, so what you sent is just below the original code in the Visual Basic window.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro keyboard commands and storing them swepo Word 7 12-15-2018 11:44 AM
Array to iterate through variables and trap blank variables Marrick13 Word VBA 5 08-04-2015 06:19 AM
Input Box Help/Storing Variables in other Macros Code for storing new word fahmiakbar Word VBA 2 02-13-2015 11:26 AM
can word: make variables, find appropriate pages, fill out pages with variables, print only those 20GT Word VBA 1 10-15-2014 09:48 PM
Creating Wizards with Input Variables marka Excel Programming 1 11-11-2012 09:43 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft