• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

Can't attach .bas file as Module in Excel

Hi Experts,

Currently I'm having problem to attached .bas file as a module when send it through email. Hope Expert can debug this error.

The error message at line:

---> Excel.Application.VBE.ActiveVBProject.VBComponents.Import (lcBasFile)

I've attached the .bas (actually the file is created as "copy_paste.bas" but after attached it in EE its became "copy-paste.bas", I don't know why.  

Hope Expert can help. I'm using Excel-2003
Sub SendEmail()
Dim wb As Workbook
Dim ws As Worksheet
Dim fName As String
Dim SendTo As String
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim pWord As String

'Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

pWord = "rbs"
SendTo = "thevarajan_subramanin@astro.com.my"
fName = "Weekly Summary Report"

Application.DisplayAlerts = False


'Copy to New Sheet '
Sheets(fName).Copy
Set ws = ActiveSheet
With ws
    .Unprotect (pWord)
    With .Range("A1", .Range("A1").End(xlUp))
      .Copy
      .PasteSpecial Paste:=xlPasteFormats
      .PasteSpecial Paste:=xlPasteValues
    End With
    .Range("A30").Formula = "=hyperlink(""\\Poaabc04\regional programming\RBS Prime Data\RBS Productivity & KPI Measurement"",""For more details: Click here"")"
    .Range("A1").Select
End With

'Save Temp Copy'
Set wb = ActiveWorkbook
With wb
  .Sheets("Weekly Summary Report").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="rbs"
  .SaveAs Filename:="C:\" & fName & ".xls"
  .Close
End With

'Fill in Subject Details'
subject_ = fName & ".xls"
attach_ = "C:\" & fName & ".xls"

'Create the Email
Set MItem = OutlookApp.createitem(0)
With MItem
  .To = SendTo
  .Subject = subject_
  .attachments.Add (attach_)
  'Send the Email
  .Send
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing
'Delete File
Kill Pathname:="C:\" & fName & ".xls"
Application.DisplayAlerts = True

Dim lBasFile As String
lcBasFile = Application.ActiveWorkbook.Path & "\copy_paste.bas"
'Excel.VBE.ActiveVBProject.VBComponent
Excel.Application.VBE.ActiveVBProject.VBComponents.Import (lcBasFile)
End Sub

Open in new window

copy-paste.bas.txt
0
Theva
Asked:
Theva
  • 10
  • 9
1 Solution
 
LordArokhCommented:
Hi Theva

Can you attach your Excel File also ? I'll take a look at the problem.

Cheers
TK,


0
 
LordArokhCommented:
Hi Theva,

No need to attach xls file. I got it from yor other question. I'll look at it right now.

Cheers

TK
0
 
LordArokhCommented:
Hi Theva

Actually I did not saw any problems besides macro security. Please check that your excel macro options are set to "TRUST ACCESS To VISUAL BASIC PROJECTS" .
 Here is how to check it :
    Menu Tools / Macro / Security ( Image STEP_1 )
    On Security Tab - My settings are set to LOW ( Image STEP_2)
    On Trusted Publishers Tab - TRUST ACCESS To VISUAL BASIC PROJECTS checked ( Images STEP_3)

You can download both files ( bas,xls ) from the link below :

http://rapidshare.com/files/325682447/Theva.zip

Just extract them and put them in one folder.

Now your good to go.

Cheers,
TK
Error-1004.JPG
Step-1.JPG
Step-2.JPG
Step-3.JPG
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
ThevaAuthor Commented:
Hi LordArokh,

Now with new kind of error message, attached the print screen of:
Device.png
Device-debug.png
0
 
LordArokhCommented:
Check NTFS permissions of the .BAS file.
If necessary take the owner ship.

It is simply I/O error. Means that system can't read the file.

Cheers,
TK
0
 
ThevaAuthor Commented:
Hi,

Looks like all checked, here's the image. Am  I looking at right right place?
permissions.png
0
 
LordArokhCommented:
Hi Theva.

Are you able to open up BAS file with notepad ?  Try if you can open the file with Notepad..

Cheers
TK
0
 
ThevaAuthor Commented:
Hi,

Yes, I can.
0
 
LordArokhCommented:
The lat advise that I can give you is,

Open the file BAS with notepad and copy the procedure. Delete the file.
Then create a new module in VB editor. and paste the procedure in it. Then on the project properties windows, right click over the module that you created ( the module that you paste your code in it ). Choose export. You will be asked to save file somewhere. Save the file in the same directory as your XLS file. File name should be "COPY_PASTE".

Cheers
TK,
0
 
ThevaAuthor Commented:
Hi,

Have tested, shows same error message result :(

Does this error happened because of sheet protection in my e-mail procedure?
0
 
LordArokhCommented:
Try Following :

Open a new excel workbook. Then VB editor and try to import the copy_paste.bas manually ?
Lets check if error releated with your XLS or my .BAS file.

Cheer,
TK
0
 
ThevaAuthor Commented:
Hi,

I can import the .bas file into excel easily (automatically creates module)
0
 
LordArokhCommented:
So try this out then.

Delete the XLS file That I sent you. And use one that you send me. Don't forget to put the BAS file to the same folder.Try then...

Maybe something  in my XLS file is not compatible with your excel.

Cheers,
TK
0
 
ThevaAuthor Commented:
Hi,

Actually I've tested both xls files. looks like I have to insert the module file manually for reports creation. Thanks for your relentless help to get this thing up and I'm really appreciated with your support. For some reason its still showing me an error message, nevertheless, we've tried our level best. Thanks.    
0
 
LordArokhCommented:
Sorry Mate,

That error really bug me up, cause they are working flawlessly here , Under MS Office 2007 and MS Office 2003.

0
 
ThevaAuthor Commented:
Hi LordArokh,

Is that any chance for us to re-script the .bas file with condition. Let say, at

Case 1
lcTargetCell = "B2"

Condition to copy the data in "B2" if the name at column "C2" in "detail Task" sheet is "Exec-1", and similar condition for Case 2, copying the data in "B7" if the name in "detail Task" is "Exec-2" and so on.

By doing so I can stars generate  the report without waiting for a specific number of files.

Hope this can be achieved
0
 
ThevaAuthor Commented:
Hi LordArokh,,

Hope you can help to revise the script so that I can perform the report creating process without waiting for the whole files. Please let me know whether this is achievable or not, if not I just used the existing one.  
0
 
LordArokhCommented:
Can you open a new question with your demand and add your XLS file.So that I can better follow up the question.
This thread got too long, lol

Cheers,
TK
0
 
ThevaAuthor Commented:
Thanks a lot
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now