Open Excel file from Access

firstlast-gzzesnwv · 2005-03-30T01:11:00.000Z

Hi, I have a simple question for you. I am using MS Access to develop a app. In one button(““Open Excel””), when clicked, it will open a default MS Excel file, assuming the Path and File name was as following: ““C:Test FolderTest.xls”” I did check this forum, and tried several time, but failed. Thanks

michellebarron · 2006-06-29T00:12:00.000Z

I was trying to do the same thing. Here is the code that worked for me. This is the event procedure on a command button.

Private Sub View_Click()

Dim intFlFound As Integer
Dim WExcel As Object
Dim WBook As Object
Dim WSheet As Object
Dim PATH_NAME As String

PATH_NAME = “C:SimInvInvoice”
With Application.FileSearch
.NewSearch
.LookIn = PATH_NAME
.SearchSubFolders = True
.Filename = CStr(InvoiceID.Value) & “*.xls”
.MatchTextExactly = False

If .Execute > 0 Then
Set WExcel = CreateObject(“Excel.Application”)
Set WBook = WExcel.Workbooks.Open(.FoundFiles(1))
Set WSheet = WBook.ActiveSheet
WExcel.Visible = True
Else
MsgBox “There were no files found.”
End If
End With
End Sub

previoustoolboxuser · 2005-04-22T20:38:00.000Z

///////////////////////////////////////////////////////
///////////////////////////////////////////////////////

My apologies. STR inserts a leading space in the string representation of a number to imply a positive number. We need to trim it out.

Replace Str(InvoiceNo.Value) with:
MID(Str(InvoiceNo.Value),2)

Regards,
John

///////////////////////////////////////////////////////
///////////////////////////////////////////////////////

access-l@Groups.ITtoolbox.com wrote:

<
<
< Thanks for your patience=2E=2E=2E I’ve tried your code and I’m getting=
< a “There were no files found” error, when it should find one=2E=
< Any ideas?
<
< May it be a problem with the "=2EFilename =3D Str(InvoiceNo=2EValue) &=
< “=2Exls"" line? Another user told me I need to turn InvoiceNo=
< into a string=2E=2E=2E
<
< This is my code:
<
<
< Private Sub Command51_Click()
< Dim WExcel As Excel=2EApplication, WBook As Excel=2EWorkbook, WSheet=
< As Excel=2EWorksheet
< Dim PATH_NAME As String
<
< Set WExcel =3D New Excel=2EApplication
<
< PATH_NAME =3D “P:chantelleCompanyBOOKKEEPINGSTUFFINVOICES”
<
< Set fs =3D Application=2EFileSearch
< With fs
< =2ELookIn =3D PATH_NAME
< =2ESearchSubFolders =3D True
< =2EFilename =3D Str(InvoiceNo=2EValue) & "=2Exls”
< If =2EExecute > 0 Then
<
< Set WBook =3D WExcel=2EWorkbooks=2EOpen(=2EFoundFiles(1))
< Set WSheet =3D WBook=2EActiveSheet
< WExcel=2EVisible =3D True
< Exit Sub
<
< Else
< MsgBox “There were no files found=2E”
< End If
< End With
<
< End Sub
<
<
<

previoustoolboxuser · 2005-04-21T03:39:00.000Z

I run mulitple versions of Excel and have not had any problems. One issue that could come up is that if the lower (older) version is
in place and the high version is not specifically linked then you might only have access to the older versions functionality.

Michael

spiceuser-2stcfvzn · 2005-04-20T14:22:00.000Z

I’m using this code through Access (VBA)… I’m wondering if it matters that I have two versions of Excel installed. Will it just open the default?

Do I maybe have to apply references in Excel?

previoustoolboxuser · 2005-04-20T09:00:00.000Z

Hello there,

Are you using this through Access (VBA) or another program maybe (vb.net)=
???
=20
Afroditi Pratta

=C1=F6=F1=EF=E4=DF=F4=E7 =D0=F1=DC=F4=F4=E1
=C4=E9=E5=FD=E8=F5=ED=F3=E7 =C4=E9=E1=E4=F1=E1=F3=F4=E9=EA=FE=ED =D5=F0=E7=
=F1=E5=F3=E9=FE=ED
=C5=D4=C1=C9=D1=C9=C1 =D3=D4=CF=C9=D7=C7=CC=C1=D4=D9=CD =C1.=C5.
Email: pratta@betting.gr

spiceuser-2stcfvzn · 2005-04-19T15:48:00.000Z

Thanks! Hopefully this is it… now I’m getting a “run-time error -2147417851 (80010105): Automation error The server threw an exception” on this line:

Set WBook = WExcel.Workbooks.Open(.FoundFiles(1))

previoustoolboxuser · 2005-04-19T05:40:00.000Z

Hello there,

You also have to include in your references “Microsoft Office XX Object L=
ibrary” (were XX is the version of office you have installed).

Take Care
Afroditi Pratta =20

=C1=F6=F1=EF=E4=DF=F4=E7 =D0=F1=DC=F4=F4=E1
=C4=E9=E5=FD=E8=F5=ED=F3=E7 =C4=E9=E1=E4=F1=E1=F3=F4=E9=EA=FE=ED =D5=F0=E7=
=F1=E5=F3=E9=FE=ED
=C5=D4=C1=C9=D1=C9=C1 =D3=D4=CF=C9=D7=C7=CC=C1=D4=D9=CD =C1.=C5.
Email: pratta@betting.gr

spiceuser-2stcfvzn · 2005-04-18T18:18:00.000Z

Sorry… I just forgot to mention it.

FileType = msoFileTypeExcelWorkbooks

previoustoolboxuser · 2005-04-18T16:36:00.000Z

When you step through the code, which line has the error.

(Folks, if you have an error coming from VBA code, and you ain’t stepping thru
to find the line, you ain’t playin’ the game right.)

HTH

Phil

From: “chantelle via access-l”

spiceuser-2stcfvzn · 2005-04-18T16:22:00.000Z

I seem to be getting a run-time error 5: invalid procedure call or argument.

I do have Excel 10.0 Library set in references… any ideas?

previoustoolboxuser · 2005-04-15T05:14:00.000Z

Hello there,

I believe that is what you want to do
‘’**********************************************************************

Private Sub Command51_Click() Dim intFlFound As Integer, strDllNms As =
String
Dim WExcel As Excel.Application, WBook As Excel.Workbook, WSheet As E=
xcel.Worksheet
Dim PATH_NAME As String

PATH_NAME =3D “P:chantelleCompanyBOOKKEEPINGSTUFFINVOICES”
With Application.FileSearch
.NewSearch
.LookIn =3D PATH_NAME
.SearchSubFolders =3D True
.Filename =3D CStr(InvoiceNo.Value) & “*.xls”
.MatchTextExactly =3D False
.FileType =3D msoFileTypeExcelWorkbooks
If .Execute > 0 then=20
Set WExcel =3D New Excel.Application
Set WBook =3D WExcel.Workbooks.Open(.FoundFiles(1))
Set WSheet =3D WBook.ActiveSheet
WExcel.Visible =3D True
Else
MsgBox “There were no files found.”
End If
End With
End Sub
‘’***********************************************************************=

NOTES:

Do not forget the property “.MatchTextExactly =3D False” (it assumes d=
efault value true).
Including the “.FileType” (if it is fixed) helps.

Take Care
Afroditi Pratta

=C1=F6=F1=EF=E4=DF=F4=E7 =D0=F1=DC=F4=F4=E1
=C4=E9=E5=FD=E8=F5=ED=F3=E7 =C4=E9=E1=E4=F1=E1=F3=F4=E9=EA=FE=ED =D5=F0=E7=
=F1=E5=F3=E9=FE=ED
=C5=D4=C1=C9=D1=C9=C1 =D3=D4=CF=C9=D7=C7=CC=C1=D4=D9=CD =C1.=C5.
Email: pratta@betting.gr

spiceuser-2stcfvzn · 2005-04-14T17:11:00.000Z

Thanks for your patience=2E=2E=2E I’ve tried your code and I’m getting=
a “There were no files found” error, when it should find one=2E=
Any ideas?

May it be a problem with the “=2EFilename =3D Str(InvoiceNo=2EValue) &=
“*=2Exls”” line? Another user told me I need to turn InvoiceNo=
into a string=2E=2E=2E

This is my code:

Private Sub Command51_Click()
Dim WExcel As Excel=2EApplication, WBook As Excel=2EWorkbook, WSheet=
As Excel=2EWorksheet
Dim PATH_NAME As String

Set WExcel =3D New Excel=2EApplication

PATH_NAME =3D “P:chantelleCompanyBOOKKEEPINGSTUFFINVOICES”

Set fs =3D Application=2EFileSearch
With fs
=2ELookIn =3D PATH_NAME
=2ESearchSubFolders =3D True
=2EFilename =3D Str(InvoiceNo=2EValue) & “*=2Exls”
If =2EExecute > 0 Then

Set WBook =3D WExcel=2EWorkbooks=2EOpen(=2EFoundFiles(1))
Set WSheet =3D WBook=2EActiveSheet
WExcel=2EVisible =3D True
Exit Sub

Else
MsgBox “There were no files found=2E”
End If
End With

End Sub

spiceuser-8tuq09x1 · 2005-04-14T12:49:00.000Z

I also needed to set references to microsoft office 10.00 object library.
Lewie

previoustoolboxuser · 2005-04-13T23:36:00.000Z

those ‘3D’ artifacts come from the various email systems the doc goes th=
rough in whatever format is is in at the time. I try to use the text =
version of my email for that very reason. Near as I can figure out it=
comes from somthing the HTML version does or goes through. I think s=
omething similar when we see an email all “run together” with no paragraphs=
, etc.
Some user groups, in fact, insist that all emails be in text format and =
will not accept HTML versions. I hope our group never makes that deci=
sion. I would rather live with the 3D’s than be restricted as to outp=
ut.

previoustoolboxuser · 2005-04-13T21:25:00.000Z

/////////////////////////////////////////////////
/////////////////////////////////////////////////

Actually, you may have been incorrectly informed; I cannot dim as filesearch so I will stand by Dimming As Object.

Regards,
John

/////////////////////////////////////////////////
/////////////////////////////////////////////////

access-l@Groups.ITtoolbox.com wrote:

<
< # 180 SAP installations in 180 days Implementation made simple.
< # Read the HP white paper: http://www.ITtoolbox.com/i/rd.asp?i=9949
<
< # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808
<
<
<
<
<
< I need to do some reading up…I got away with dimming fs as an object
<
< PS: Where are those ‘3D’ artifacts coming from??
< John
<
<
<
<
< access-l@Groups.ITtoolbox.com wrote:
<
< <
< <
< < No what I meant was it wasn’t declared.
< < Dim fs as …
< < Sorry for the confusion.
< < Found out it was type filesearch
< < Dim fs as filesearch.
< < Thanks
< < Lewie
< <
< <

previoustoolboxuser · 2005-04-13T21:18:00.000Z

I need to do some reading up…I got away with dimming fs as an object

PS: Where are those ‘3D’ artifacts coming from??
John

access-l@Groups.ITtoolbox.com wrote:

<
<
< No what I meant was it wasn’t declared.
< Dim fs as …
< Sorry for the confusion.
< Found out it was type filesearch
< Dim fs as filesearch.
< Thanks
< Lewie
<
<

previoustoolboxuser · 2005-04-13T21:14:00.000Z

////////////////////////////////////////////////////////
////////////////////////////////////////////////////////

JoAnn
You are completely right to pursue this. I need to clean up some bad habits. I would dim the dead dog as an Object.

PS: I usually have a loop to iterate the filesearch colllection.

Regards,
John
////////////////////////////////////////////////////////
////////////////////////////////////////////////////////

access-l@Groups.ITtoolbox.com wrote:

<
< # Solve technical hardware problems, evaluate vendors, find jobs.
< # Hardware Knowledge Base: http://www.ITtoolbox.com/i/rd.asp?i=9987
<
< # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808
<
< Assuming you have Option Explicit at beginning of code module, DEAD_DOG
< would only work if it had been defined as something. I think that was the
< question. What object should fs be defined as? (I am curious about this as
< well since in a similar piece of my code I use With Application.FileSearch
< object directly.)
<
<
< JoAnn
<
<
<

joannross-zelarbht · 2005-04-13T17:28:00.000Z

Assuming you have Option Explicit at beginning of code module, DEAD_DOG
would only work if it had been defined as something. I think that was the
question. What object should fs be defined as? (I am curious about this as
well since in a similar piece of my code I use With Application.FileSearch
object directly.)

JoAnn

spiceuser-8tuq09x1 · 2005-04-13T17:25:00.000Z

Thanks that’s a beaut.
Got it down to a button.
Lewie