I'm currently working on a program with Visual Basic 6. However I'm having trouble accessing Excel files. Can anyone tell me exactly how to code a command button so as to display an excel spreadsheet once the command button is clicked?
Any help on this matter would be greatly appreciated.
You should be able to use the search engine to find many examples already posted.
I would also rename your own thread - current title doesn't tell us much...
Rhino has a good suggestion...many threads would never occur if you search first the internet, then this forum, for your issue. However, I do have a question....are you looking to INVOKE (that is, start Excel and show the spreadsheet in Excel) Excel with your button, or display excel DATA (say in a flexgrid) in a VB6 program? Makes a difference for what to search. The former, you would search for things like Shell, or ShellExecute. For the latter, search for something like "running (using) excel in VB6".
Yup, your Subject title is not informative.
Also your details are brief, and not telling us much.
I used to offer $20 to the first responder to guess what an OP (Original Poster) really meant.
One possibility is that you wish to to open an excel spreadsheet in Excel (Start up Excel and display some file that the user has indicated/selected within your VB6 program).
If my guess is correct then you should use the ShellExecute API.
What that will do is open the desired file in it's default program.
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
Private Const SW_SHOWNORMAL = 1
Private Sub Command1_Click()
Dim sPathAndFile As String
sPathAndFile = App.Path & "\" & "vbtest.xls"
Call OpenThisFileInItsDefaultProgram(sPathAndFile)
End Sub
Private Sub OpenThisFileInItsDefaultProgram(sPathAndFile As String)
Dim result As Long
result = ShellExecute(Me.hWnd, "Open", sPathAndFile, "", "", SW_SHOWNORMAL)
End Sub
If my guess is incorrect, then -
1) File my code snippet away for future use.
2) Spend some time and care, to provide a fuller and clearer description of your problem and needs.
Thanks very much for the responses I appreciate the help. Sorry for being so vague in explaining my issue.
Bobbles is correct in that basically my issue is that I want to open an Excel spreadsheet by clicking on a command button but I'm really not sure how to do this.
I'm not really that familiar with the 'shellexecute' api though.
Bobbles I tried the code you gave me in your response however it didn't work. Could you or anyone provide some more information on how I open an Excel spreadsheet using a command button??
You need to provide more information yourself. Don't just tell us it didn't work tell us what happened. Most likely you just copied and pasted the code and did not bother to set the path and filename
The quickest way to demonstrate that the code works is to COPY one of your excel files into the same folder as your program . Rename that COPY to vbtest.xls and run the code. If your file (the one you copied from) has a different extension from.xls, then use that extension.
Try that (Also I will attach an example project soon)
Bobbles thanks very much that example you gave me it works perfectly. However, when I change the Excel file from vbtest to any other excel file I have, nothing happens when I click the command button when the program is run. I tried renaming a copy of one of my files to vbtest.xls too and the same same result...nothing happened when i clicked the command button. Any ideas on why this might be?
Can you use the folder that you created in response to my last post EG C:\Test_Robs_Pgm
Place your alternate file into that folder, and amend your code to attempt to run it.
If it does not run then save the project, and close VB
Then zip up the whole folder and attach it here.
I will then try to run it, and will find out what is wrong for you.
Bobbles I have a folder created with your example and one of my excel files in it. When I amended my code to run my own excel file it still didnt work.
OK Got it working
Just comment out this line -
'sPathAndFile = App.Path & "\" & "vbtest.xls"
And replace it with this line -
sPathAndFile = App.Path & "\" & "Asset List With Asset Numbers.xls"
Ok brilliant thats working very well. I even moved different excel files into that Robs pgm folder and changed the code and the different Excel files opened up perfectly. However, when I went to use this code on my main project that Im working on it wouldnt work in that project. I copied the code exactly how you had it and ran my program but it wouldnt work. When I click the command button, Excel should open but instead, nothing happens. I dont get it, it works perfectly for me on your own example but not on my own project. Any idea what the problem might be?
Really sorry to keep annoying you about this issue but its very important to the project Im working on that I get this right.
I am about to go to bed.
It probably is the case that you are not setting the correct path to the Excel file.
I assume that the file will not be in the same folder as the EXE (your program) ?
I have created a folder called RobsExcelFileIsInHere and I have placed your file into there.
The code below has been modified so that the program is setting the path to the Folder + file
I have actually created such a folder (with your file in it), and it works.
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
Private Const SW_SHOWNORMAL = 1
Private Sub Command1_Click()
Dim sPathAndFile As String
sPathAndFile = "C:\RobsExcelFileIsInHere\" & "Asset List With Asset Numbers.xls"
Call OpenThisFileInItsDefaultProgram(sPathAndFile)
End Sub
Private Sub OpenThisFileInItsDefaultProgram(sPathAndFile As String)
Dim result As Long
result = ShellExecute(Me.hWnd, "Open", sPathAndFile, "", "", SW_SHOWNORMAL)
End Sub
Nope sorry I still cant get it working. This time when I use the code in your last post exactly the way you have it, a Compile Error pops up saying 'Argument not optional'. And that takes me back to the code where the words 'ShellExecute' are highlighted in the Private Sub 'openthisfileinitsdefaultprogram'.
I've no idea why this is since I used your above code in another example and it worked perfectly.
Ok so here's the code I'm using on the command button in my project.
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
Private Sub Command1_Click()
Dim sPathAndFile As String
sPathAndFile = "F:\Slaughter hall WO's\Stun Box Monthly.xlsx"
Call OpenThisFileInItsDefaultProgram(sPathAndFile)
End Sub
Private Sub OpenThisFileInItsDefaultProgram(sPathAndFile As String)
Dim result As Long
result = ShellExecute(Me.hWnd, "Open", sPathAndFile, ",", SW_SHOWNORMAL)
End Sub
Thats exactly what my code is. The only difference from the code above that Bobbles gave me was the file I want to open. The thing I cant understand is that then I took this code and used it on a new separate project and form just to try it out it worked perfectly, but when I put in into the project Im working on, it wont work and the Compile Error pops up
You just posted while I was typing this, SO I HAVE NOT READ YOUR VERY LATEST POST YET.
A couple of members have asked you to post your code, which I cannot disagree with.
But even if you don't do that, you should take the time to answers all questions fully, and actually spell out (IN DETAIL) what you just attempted.
From your last post I cannot tell whether you tried blending my last code into your main project, or into the test project I sent you.
For example you could have said this, and YOU CERTAINLY SHOULD HAVE DONE THIS -
Create a 2nd folder C:\RobsExcelFileIsInHere for your excel file
Did you use the project that I sent before, AND REPLACE IT's CODE with that in my last post ?
I cannot tell from your last post (actually 2nd last now), whether you did that ?
I did that and it worked, so it should work for you.
But as I said, your posts are too brief for me to know what you just attempted.
Please do the above, and report back.
Rob
PS You are lucky that I have been prepared to fill in the major gaps in your posts, and do some conjecturing, and are lucky that my crystal ball was working well.
Just had a quick look at your very last post.
I don't like spaces in file and folder names, and I would never use a ' in a folder name.
Try renaming that folder.
In fact try pointing your code to the folder C:\RobsExcelFileIsInHere and the file we had in there, to see if that works.
If it does, then try putting your .xlsx file in there, and try again.
By a process of minor changes, you should be able to home in on where the problem is occurring.
Rob
PS You say a compile error. Are you using the correct terminology ?
When you run the project, does it run and stop on a line ? (Which line ?)
It is possible to run your project without compiling it.
Some errors are detected in such a run.
Later when someone decides to make an EXE (COMPILE THE PROGRAM), some other errors can then be detected. Please carefully consider what you type for us.
Last edited by Bobbles; Jan 24th, 2013 at 08:35 AM.
Your line and the line I posted earlier ARE DIFFERENT -
YOURS
result = ShellExecute(Me.hWnd, "Open", sPathAndFile, ",", SW_SHOWNORMAL)
MINE
result = ShellExecute(Me.hWnd, "Open", sPathAndFile, "", "", SW_SHOWNORMAL)
Hahahaha Oh my god its working! Its actually working! Yep Bobbles your right in your last post, all I needed to do was to double the quotes in the line you're referring to in your last post. Amazing how all this trouble and headaches boils down to two quotes! The code is now working exactly as I want it to. I set up a folder for all the files I would be using that code to open so they are all in their place and in order. I'd like to thank you Rob and everyone for all your help I really appreciate it!
[RESOLVED] VB6 Command Button
Reply With Quote
