Hi Phucon,
adding on ...
> "CstrFilePathEorG1B"
what are some of your values for this? You do not have to include the file extension, by the way -- Excel will assign the default one ;) ~
as the link Bill pointed you to mentions different versions .. I assume this is the came with you too? At some point, maybe it was a preview or a full version, but something else is providing the default library at runtime.
In the Tools, References dialog box, uncheck the Excel library that is selected, if you are doing early binding. If you are not, that is fine too -- now you will be.
Click Browse and navigate to the Excel.exe file for the version of Access you are using (don't mix versions in code). I have 2013 installed and my Excel file is here:
C:\Program Files\Microsoft Office 15\Data\Delta\root\office15\excel.exe
at some point I also put Office 365 on so if you don't have an Excel.exe file there, let me know what versions you are using and what else might have gotten installed.
adding on ...
> "CstrFilePathEorG1B"
what are some of your values for this? You do not have to include the file extension, by the way -- Excel will assign the default one ;) ~
as the link Bill pointed you to mentions different versions .. I assume this is the came with you too? At some point, maybe it was a preview or a full version, but something else is providing the default library at runtime.
In the Tools, References dialog box, uncheck the Excel library that is selected, if you are doing early binding. If you are not, that is fine too -- now you will be.
Click Browse and navigate to the Excel.exe file for the version of Access you are using (don't mix versions in code). I have 2013 installed and my Excel file is here:
C:\Program Files\Microsoft Office 15\Data\Delta\root\office15\excel.exe
at some point I also put Office 365 on so if you don't have an Excel.exe file there, let me know what versions you are using and what else might have gotten installed.
~~~ OFF TOPIC: Microsoft MVP Virtual Conference~ May 14 + 15 (Thu, Fri) ~~~
Happy Friday to Liz, hi to everyone especially Bill, ... haven't had much chance to post lately -- busy preparing for the Microsoft MVP Virtual Conference (open to all -- that means everyone ;) ~ and now the agenda is posted too. I am honored to be presenting in the Access session (yellow Consumer track) ... session 2, day 2. Right after Access is Excel -- Bill "MrExcel" Jelen talking about Power Pivot (pivot tables +). That will be a great session too. The Microsoft Office products are in the Consumer track.
The Power of Community
May 14 & 15, 8am-6pm (PST/UTC -08:00) -- 9 am to 7 pm Mountain time
Microsoft MVP Virtual Conference (V-Conf)
http://mvp.microsoft.com/en-us/virtualconference.aspx
May 14 & 15, 8am-6pm (PST/UTC -08:00) -- 9 am to 7 pm Mountain time
Microsoft MVP Virtual Conference (V-Conf)
http://mvp.microsoft.com/en-us/virtualconference.aspx
 | |||||||
Preview by Yahoo | |||||||
Last I heard, there were about 4,000 people registered. There are 5 tracks -- 3 in English, 1 in Spanish, 1 in Portugese. This is Microsoft's first ever 2-day MVP conference open to the public. Hope to see YOU online ...
~~~
Phucon, hope that you don't mind me interjecting into your thread, thanks. I put links in my signature you might find helpful for writing to Excel from Access too ~
Warm Regards,
Crystal
Download -- Document Calculated Fields in Queries, write results to Excel and format
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html
~~~
video -- Document Query Calculated Fields: Access to Excel. Music by JD Live (cc)
http://www.youtube.com/watch?v=vS8KfHU6L90
4-minute video demo, free tool and slick SQL to show calculated fields in queries, use CopyFromRecordset to put in Excel and then format
~ have an awesome day ~
Crystal
Download -- Document Calculated Fields in Queries, write results to Excel and format
http://www.rogersaccesslibrary.com/forum/document-calculated-fields-in-queries_topic619.html
~~~
video -- Document Query Calculated Fields: Access to Excel. Music by JD Live (cc)
http://www.youtube.com/watch?v=vS8KfHU6L90
4-minute video demo, free tool and slick SQL to show calculated fields in queries, use CopyFromRecordset to put in Excel and then format
~ have an awesome day ~
On Friday, May 1, 2015 2:25 PM, "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Phucon
Take a look at this thread:
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
---In MS_Access_Professionals@yahoogroups.com, <saigonf7q5@yahoo.com> wrote :
I have a function (I copied it from a book) which I have been using for exporting data to Excel. It's been working perfectly fine until recently the error "-2147319779 Automation error Library not registered." popped up each time when the Export Button was clicked. It happens on every Access application that uses this function.
The IT people had done something to the Microsoft Office Suite, I was wondering if that's the cause? Is this an easy fix and How?
Phucon
Function SendRecordset()
On Error GoTo ErrorHandler
On Error GoTo ErrorHandler
Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Add
Set db = CurrentDb()
Set f = Forms!frmRunQuery
Set xlwkbk = xl.Workbooks.Add
Set db = CurrentDb()
Set f = Forms!frmRunQuery
xl.Visible = True
xl.Application.DisplayAlerts = False 'suppress Excel's alerts
Select Case f!lstAsset
Case "E or G than 1B"
Call EorG1B
xl.ActiveWorkbook.SaveAs FileName:= _
CstrFilePathEorG1B, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Case "Less than 1B"
Call Less1B
xl.ActiveWorkbook.SaveAs FileName:= _
CstrFilePathLess1B, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Case ">=1Bil and <1Bil"
Call Both
xl.ActiveWorkbook.SaveAs FileName:= _
CstrFilePathBoth, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select
Case "E or G than 1B"
Call EorG1B
xl.ActiveWorkbook.SaveAs FileName:= _
CstrFilePathEorG1B, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Case "Less than 1B"
Call Less1B
xl.ActiveWorkbook.SaveAs FileName:= _
CstrFilePathLess1B, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Case ">=1Bil and <1Bil"
Call Both
xl.ActiveWorkbook.SaveAs FileName:= _
CstrFilePathBoth, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select
xl.Application.DisplayAlerts = True 'turn the warning back on
With xl
.Sheets("Sheet1").Delete
.Sheets("Sheet2").Delete
.Sheets("Sheet3").Delete
End With
ExitFunction:
'clean up
Set rs = Nothing
Set xl = Nothing
Set xlwkbk = Nothing
Set xlsheet = Nothing
.Sheets("Sheet1").Delete
.Sheets("Sheet2").Delete
.Sheets("Sheet3").Delete
End With
ExitFunction:
'clean up
Set rs = Nothing
Set xl = Nothing
Set xlwkbk = Nothing
Set xlsheet = Nothing
Exit Function
ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume ExitFunction
Resume
End Function
MsgBox Err.Number & vbCrLf & Err.Description
Resume ExitFunction
Resume
End Function
__._,_.___
Posted by: Crystal <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar