Save and load dialog inputs via Excel

Post Reply
mcasey
Frequent User
Frequent User
Posts: 36
Joined: Sat Jan 18, 2014 3:16 am

Save and load dialog inputs via Excel

Post by mcasey » Mon Jul 22, 2019 10:39 am

First let me apologize if I am posting this in the wrong location.

I have been working on a custom dialog that allows the operator to either key in parameters from a drawing, load previously entered values and/or save values. Overall the code is operational but I have two minor issues that I'd like to see if anyone can help me resolve.

Below is the relevant part of the dialog box code so you can see what I am doing currently.

1) Set a specific location on a network drive for selecting what Excel file to open
2) Saving the Excel file to a file name entered by the user at run time (I commented out what I was trying to do to accomplish this)


Code: Select all

If result = 2 Then 

Dim Saved_Input
Dim Saved_Input_WBook
Set Saved_Input = CreateObject("Excel.Application")
Saved_Input.visible = True
'Lines below work but would like to point them to a specific location on a network to start. 
lFileName = Saved_Input.GetOpenFilename ("Excel Files (*.xlsx),*.xlsx,All Files (*.*),*.*")
Set Saved_Input_WBook = Saved_Input.Workbooks.Open(lFileName)


Size = Saved_Input.Cells(1,2).value
Clocking = Saved_Input.Cells(2,2).value
Flange_Width = Saved_Input.Cells(3,2).value
Flange_Dia = Saved_Input.Cells(4,2).value
Flange_Z_In = Saved_Input.Cells(5,2).value
ORing_Minor_Dia = Saved_Input.Cells(6,2).value
ORing_Width = Saved_Input.Cells(7,2).value
ORing_Depth = Saved_Input.Cells(8,2).value
Rear_Ins_Dia_1 = Saved_Input.Cells(9,2).value
Rear_Ins_Dia_1_Z_In = Saved_Input.Cells(10,2).value
Rear_Ins_Dia_2 = Saved_Input.Cells(11,2).value
Rear_Ins_Dia_2_Z_In = Saved_Input.Cells(12,2).value

Saved_Input.DisplayAlerts = False
Saved_Input.Quit

ElseIf result = 1 Then
Dim Saved_Output
Dim Saved_Output_WBook
Dim Saved_Output_Sheet
Set Saved_Output = CreateObject("Excel.Application")
Set Saved_Output_WBook = Saved_Output.Workbooks.Add
Set Saved_Output_Sheet = Saved_Output_WBook.Worksheets(1)

Saved_Output.visible = True

Saved_Output_Sheet.Cells(1,1).value = "Size"
Saved_Output_Sheet.Cells(1,2).value = Size
Saved_Output_Sheet.Cells(2,1).value = "Clocking"
Saved_Output_Sheet.Cells(2,2).value = Clocking
Saved_Output_Sheet.Cells(3,1).value = "Flange_Width"
Saved_Output_Sheet.Cells(3,2).value = Flange_Width
Saved_Output_Sheet.Cells(4,1).value = "Flange_Dia"
Saved_Output_Sheet.Cells(4,2).value = Flange_Dia
Saved_Output_Sheet.Cells(5,1).value = "Flange_Z_In"
Saved_Output_Sheet.Cells(5,2).value = Flange_Z_In
Saved_Output_Sheet.Cells(6,1).value = "ORing_Minor_Dia"
Saved_Output_Sheet.Cells(6,2).value = ORing_Minor_Dia
Saved_Output_Sheet.Cells(7,1).value = "ORing_Width"
Saved_Output_Sheet.Cells(7,2).value = ORing_Width
Saved_Output_Sheet.Cells(8,1).value = "ORing_Depth"
Saved_Output_Sheet.Cells(8,2).value = ORing_Depth
Saved_Output_Sheet.Cells(9,1).value = "Rear_Ins_Dia_1"
Saved_Output_Sheet.Cells(9,2).value = Rear_Ins_Dia_1
Saved_Output_Sheet.Cells(10,1).value = "Rear_Ins_Dia_1_Z_In"
Saved_Output_Sheet.Cells(10,2).value = Rear_Ins_Dia_1_Z_In
Saved_Output_Sheet.Cells(11,1).value = "Rear_Ins_Dia_2"
Saved_Output_Sheet.Cells(11,2).value = Rear_Ins_Dia_2
Saved_Output_Sheet.Cells(12,1).value = "Rear_Ins_Dia_2_Z_In"
Saved_Output_Sheet.Cells(12,2).value = Rear_Ins_Dia_2_Z_In

'Select file name & location to save to
'sFileName = Saved_Output.GetSaveAsFilename ("P:\CMM Programs\Expertimental programs\Project SkyNet\Data\Recipes\PN_Rev","Excel Files (*.xlsx),*.xlsx,All Files (*.*),*.*")

'THIS LINE DOES NOT WORK
'Saved_Output_Workbooks.SaveAs "P:\CMM Programs\Expertimental programs\Project SkyNet\Data\Recipes\" & sFileName & ".xlsx"

Saved_Output.DisplayAlerts = False
Saved_Output.Quit
Any help would be greatly appreciated

User avatar
CrashN8
Site Admin
Site Admin
Posts: 579
Joined: Thu Feb 09, 2017 4:07 pm

Re: Save and load dialog inputs via Excel

Post by CrashN8 » Tue Jul 23, 2019 10:29 am

Here is a previous pst that describes using Windows Common Dialog for file selection. The Script sets a variable 'MyFile' to the path of file selected by user in the Windows Common Dialog.

viewtopic.php?f=31&t=1662

It is possible to make a slightly different VBScript that allows user to choose Folder vs. File as shown below. I've updated the referenced post too.

Code: Select all

Dim objFolder, objShell
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, "Please select the folder.", 1, "")

MyFolder = objFolder.Self.path

MsgBox("You picked the folder:" + Chr(10) + MyFolder)

User avatar
CrashN8
Site Admin
Site Admin
Posts: 579
Joined: Thu Feb 09, 2017 4:07 pm

Re: Save and load dialog inputs via Excel

Post by CrashN8 » Tue Jul 23, 2019 12:14 pm

Here is an update... I have included a Folder Selection dialog and InputBox() for specifying File Name. The Excel Application remains open while displaying data that was sent by VBScript until user clicks OK on the InputBox() dialog.

Code: Select all

Dim objFolder, objShell
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, "Please select the folder.", 1, "")

MyFolder = objFolder.Self.path
'MsgBox("You picked the folder:" + Chr(10) + MyFolder)

Dim OExcel 
Dim Obook
Dim Osheet
set OExcel=CreateObject("Excel.Application")
set Obook=OExcel.Workbooks.add
set Osheet=Obook.Worksheets(1)

OExcel.visible = True

Osheet.Range("A1").Value = "Size"
Osheet.Range("A2").Value = Size
Osheet.Range("B1").Value= "Clocking"
Osheet.Range("B2").Value = Clocking
Osheet.Range("C1").Value = "Flange_Width"
Osheet.Range("C2").Value = Flange_Width

'Select file name to save as
MyFile = InputBox("Enter File Name", "Saving to " + MyFolder, "MyFile")
MyFilePath = MyFolder + "\" + MyFile + ".xlsx"
'MsgBox(MyFilePath)

Obook.SaveAs MyFilePath
OExcel.DisplayAlerts = False
OExcel.Quit
folder_selection_2.jpg
You do not have the required permissions to view the files attached to this post.

Post Reply