Function in Access to Open Excel adn Save as .pdf 2

[Tekhelpnet]

Helo everybody!

I am going to try my best to explain.
Just got a new job.
I am System Analyst and writing some Reports.
I just saw few Excel Reports that are suppose to be saved as pdf file and send by email to clients.
It takes ennormous amount of time to do this.
I was wondering if I can write a function in Access that will open Excel (I have to call Excel Application somehow)file using it's path and save every worksheet (Name_Date)in Path directory.
Then I could simply take these files and post them.
It would save me days.
I am open for ideas. I use to have this function years ago but I forgot completely how to write them.
Please, help me to find a way.
Thanks

 

[Tekhelpnet]

I am trying to start

Public Sub OpenExcelAPP_Click()

Dim ExcelAPP As Excel.Application

Set APP = GetObject(, "excel.application")
-----------------------

However I am getting error that Excel.Application is user-defined type not defined.
GetObject is also getting an error at "excel.application" part.

Anyone knows why?

 

[traingamer]

Have you added a reference to Microsoft Excel 11.0 Object Library or its equivalent?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Tekhelpnet]

I forgot but I was just coming to say I DID and it works now at the first line however this one gets error
Set APP = GetObject(, "excel.application")
ActiveX component can not create an object.
Thanks

 

[PHV]

Set APP = CreateObject("Excel.Application")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

Tekhelpnet

Set APP = GetObject(, "excel.application")

tries to grab a running instance of excel. If none exists then you get the error.

Set APP = CreateObject("Excel.Application")
gets you a fresh new instance of excel

If you put all this together you could open an excel instance anyway and finish with a happy user.

Sub Tekhelpnet()
Dim objExcel As Object 'Late binding for versionning tranquility!
Dim bWasExcelRunning As Boolean
On Error Goto 0
Set APP = GetObject(, "excel.application")
If Err <> 0 Then
   bWasExcelRunning= False
   Set APP = CreateObject("Excel.Application")
Else
   bWasExcelRunning= True
End If
Err.Clear
On Error Goto yourErrorHandler
....

Exit_Here:
   If Not objExcel Is Nothing Then
       If Not bWasExcelRunning Then objExcel.Quit 
'Happy user for letting his workbooks still open after all the mess 
       Set objExcel = Nothing 
'Do not forget if you set any variables to any excel object, to destroy them!
   End If
   Exit Sub

yourErrorHandler:
.....

End Sub

Keep in mind that you should refer to objects of excel explicity. That is a must 'cause you 'll have ghost excel running

 

[Tekhelpnet]

THANKS
How do I proceed if I need to GetObject from known location and it is kind of a template that sits there?
I have to set
FilePath='C:\Test\Excel.xls' - that I know but how do I navigate TO this file?
GetObject(FilePath)??????
Thanks

 

[PHV]

No F1 key on your keyboard ?

 

[Tekhelpnet]

I had declared:
Dim FilePath as string
FilePath='C:\Test\Excel.xls'
Set xl = GetObject(FilePath)
gives me type mismatch error.


Thanks

 

[PHV]

How is dimmed xl ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tekhelpnet]

Dim xl As Excel.Application

Thanks for links.

 

[JerryKlmns]

Something like

Dim objWrkBook As Excel.Workbook

Set objWrkBook = objExcel.Workbooks.Open FilePath

...
objWrkBook.Save
objWrkBook.close
Set objWrkBook = Nothing
End Sub

 

[PHV]

Dim xl As Excel.Application
Set xl = GetObject(FilePath).Application

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tekhelpnet]

Works a charm but Excel comes up with no worksheets even it has 3 worksheets at the FilePath location.

I am using method below and it does open with worksheets.
Dim G As Long
G = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strDocPath, vbNormalFocus)

However I am curious why is this method not opening sheets?
Set xl = GetObject(FilePath).Application

I like simpler solutions better so I would love to use
Set xl = GetObject(FilePath).Application

Thanks

 

[JerryKlmns]

I think that this

Set xl = GetObject(FilePath)

should have xl to be of Excel.Workbook.

But if you Dim xl As Object you 'll have it going

 

[Tekhelpnet]

Thanks,
I am moving forward and got stuck again.
I have

Dim G As Long
Dim CurWkbook As Workbook
Dim wkSheet As Worksheet
Dim i As Integer

strDocPath = "C:\Test\TestReport.xls"
G = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strDocPath, vbNormalFocus)
--------------------------------
THIS OPENS MY EXCEL FILE
--------------------------------
NOW I want to save every worksheet separately
---------------------------------------------
Set CurWkbook = ActiveWorkbook
wkSheet = ActiveWorkbook.Sheets.Count !ERROR HERE!
(Object variable or With block variable not set)
i = i + 1
For Each wkSheet In CurWkbook.Worksheets
wkSheet(i) = i + 1
---------------------------------------------
I remember it goes like
ActiveWorkbook
ActiveWorkbook.Sheets and THEN
ActiveWorkbook.Sheets.Count

What am I missing?
Thanks

 

[Tekhelpnet]

I am sorry I had to re-write everything and here what i've gotten so far.

Option Compare Database
Option Explicit

Dim xl As Excel.Application
__________________________________________
Public Sub OpenDocument()

Dim G As Long
Dim strDocPath As String
strDocPath = "C:\Test\TestReport.xls"

G = Shell("RUNDLL32.EXE URL.DLL,FileProtocolHandler " & strDocPath, vbNormalFocus)

Call SaveSingleSheet
End Sub
_______________________________________________________
Sub SaveSingleSheet()
'This will copy "Sheet1" to a new workbook, give it a name, save it and close it

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="C:\Sheet1.xls"
ActiveWorkbook.Close
xl.Quit

End Sub
___________________________________________________________

I think it looks more compact and next step for me here is to have SaveSingleSheet() make into SaveEachSheet()
using
For Each WorkSheet in Workbook

Can you please, push me in a right direction? Thanks

 

[PHV]

A starting point (typed, untested):

Public Sub OpenDocument()
Dim xl As Object, wb As Object, sh As Object
Dim strDocPath As String
strDocPath = "C:\Test\TestReport.xls"
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(strDocPath)
For Each sh In wb.Worksheets
  sh.Copy
  xl.ActiveWorkbook.SaveAs FileName:="C:\" & sh.Name & ".xls"
  xl.ActiveWorkbook.Close
Next
wb.Close
xl.Quit
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tekhelpnet]

Thanks PHV!
It is going well now.

 

[JerryKlmns]

Do add at the end

wb.Close
[b]Set wb = Nothing[/b]
xl.Quit
[b]Set xl = Nothing[/b]

End Sub