About VB use common dialog component problem, a great god answers.-CodePudding

Specific situation is like this, I want to write a program that need to open an existing excel spreadsheet to calculate, and the results generated new excel and save,
At first I was written like this:

 Dim excel_App As Excel. Application 
Dim excel_Book As Excel. The Workbook 
Dim excel_sheet As Excel. The Worksheet 
The Set excel_App=CreateObject (" Excel. Application ") 
Excel_App. Visible=False 
Set excel_Book=excel_App. Workbooks. Open (App) Path + "\ A file. XLSX") 
The Set excel_sheet=excel_Book. Worksheets (" Sheet1 ") 
Dim As Integer I 
Dim j As Integer 
Dim matrix (1 To 10, 1 To 12) As an Integer 
Dim a (1 To 10, 1 To 12) As an Integer 
Dim b (1 to 10, 1 to 12) as an integer 
For I=1 To 10 
For j=1 To 12 
A (I, j)=excel_sheet. Cells (I + 1, j + 1) 
B (I, j)=a (I, j) + 3 
Excel_sheet. Cells (I + 1, j + 1)=b (I, j) 

Excel_Book_b. SaveAs FileName:=App. Path + "B file. XLSX 
"
The Set excel_sheet=Nothing 
Excel_App. ActiveWorkbook. Close SaveChanges:=False 
The Set excel_Book=Nothing 
Excel_App. Quit 
The Set excel_App=Nothing

But I find that writing, it is necessary to put the file into the program with the directory, is not very convenient,
So I just want to use the common dialog, each time you run the program to choose of excel, read the application to the data, finally, operations, reoccupy file dialog stored in a designated location, could you tell me how to write the program?

CodePudding user response:

You already know the vb common dialog, baidu should have a lot of examples:)

CodePudding user response:

Comdlg. ShowOpen
Comdlg. The FileName is what you want

CodePudding user response:

refer to the second floor caozhy response:

comdlg. ShowOpen
Comdlg. The FileName is your

But I need is not open, also need to open the excel to operate

CodePudding user response:

reference weixin_38595659 reply: 3/f

Quote: refer to the second floor caozhy response:

Comdlg. ShowOpen
Comdlg. The FileName is your

But I need is not open, also need to open the excel operate.

Common dialog can only give you return to chose which file (or "what"), specific "action" it is not possible for you to complete!

Since the use of Excel. The Application object, that why still to use CommonDialog, direct call ExcelApp object,

 Private Sub Command1_Click () 
Dim excel_App As Excel. Application 
Dim excel_Book As Excel. The Workbook 
Dim excel_sheet As Excel. The Worksheet 
Dim strFileName As String 

The Set excel_App=CreateObject (" Excel. Application ") 
Excel_App. Visible=False 
'display dialog box to select a file 
With excel_App. FileDialog (1) 
. AllowMultiSelect=False 
FilterIndex=2 
'Show function display dialog FileDialog object is created, and return 1 (open) or 0 (cancelled), 
If (0 & amp; .=Show (), Then the Exit Sub 'cancel 
StrFileName=. SelectedItems (1) 
End With 
MsgBox "choose file is:" & amp; StrFileName, 64 & amp; 
The Set excel_Book=excel_App. Workbooks. Open (strFileName) 
The Set excel_sheet=excel_Book. Worksheets (" Sheet1 ") 
'other operating... 
'... 
'... 

Save the file: 
 'With excel_App. FileDialog (2) 
If (1 & amp; Then.=Show ()) 
StrFileName=. SelectedItems (1) 
'excel_Book_b. SaveAs FileName:=App. Path + "B file. XLSX 
"Excel_Book. SaveAs FileName:=strFileName 
End the If 
End With 

The Set excel_sheet=Nothing 
Excel_App. ActiveWorkbook. Close SaveChanges:=False 
The Set excel_Book=Nothing 
Excel_App. Quit 
The Set excel_App=Nothing 
End Sub

CodePudding user response:

reference 4 floor Chen8013 response:

Quote: reference weixin_38595659 reply: 3/f

Quote: refer to the second floor caozhy response:

Comdlg. ShowOpen
Comdlg. The FileName is your

But I need is not open, also need to open the excel operate.

Common dialog can only give you return to chose which file (or "what"), specific "action" it is not possible for you to complete!

Since the use of Excel. The Application object, that why still to use CommonDialog, direct call ExcelApp object,

 Private Sub Command1_Click () 
Dim excel_App As Excel. Application 
Dim excel_Book As Excel. The Workbook 
Dim excel_sheet As Excel. The Worksheet 
Dim strFileName As String 

The Set excel_App=CreateObject (" Excel. Application ") 
Excel_App. Visible=False 
'display dialog box to select a file 
With excel_App. FileDialog (1) 
. AllowMultiSelect=False 
FilterIndex=2 
'Show function display dialog FileDialog object is created, and return 1 (open) or 0 (cancelled), 
If (0 & amp; .=Show (), Then the Exit Sub 'cancel 
StrFileName=. SelectedItems (1) 
End With 
MsgBox "choose file is:" & amp; StrFileName, 64 & amp; 
The Set excel_Book=excel_App. Workbooks. Open (strFileName) 
The Set excel_sheet=excel_Book. Worksheets (" Sheet1 ") 
'other operating... 
'... 
'... 

Save the file: 
 'With excel_App. FileDialog (2) 
If (1 & amp; Then.=Show ()) 
StrFileName=. SelectedItems (1) 
'excel_Book_b. SaveAs FileName:=App. Path + "B file. XLSX 
"Excel_Book. SaveAs FileName:=strFileName 
End the If 
End With 

The Set excel_sheet=Nothing 
Excel_App. ActiveWorkbook. Close SaveChanges:=False 
The Set excel_Book=Nothing 
Excel_App. Quit 
nullnullnull