Import .BAS file into Excel VBE window from Access VBE

[bcn1988]

Hello,

I have an excel file that has been exported from Access. Since this is a brand new file, there are no modules saved to this excel spreadsheet. I have been attempting to import a saved .BAS file to EXCEL VBE with VBA code from Access VBE. I am able to import the .BAS file, however the module is being imported into Access VBE when I need it to upload into Excel VBE. Please see the code below that I have in Access VBE.


Sub TestImportBAS()

Dim TheDate As String
Dim TheDate1 As String
Dim FileDirectory As String
TheDate = Format(Date, "MM-DD-YYYY")
TheDate1 = Format(Date, "MMDDYYYY")



FileDirectory = "C:\Documents and Settings\htq972\Desktop\Work Files\VBA Modules\POR & PMOR VBA\Excel Modules\2nd Module\Rename_Export_Tabs.bas"

Application.VBE.ActiveVBProject.VBComponents.Impor t (FileDirectory)
End Sub



Please help! I need to roll this out ASAP! Thanks

[ssanfu]

I would start by opening Excel and recording a macro. Record the steps you would do to insert a new module and import the .BAS file.
That will show you the VBA required to create the module and import the bas file.

In Access, the Access VBA code would need to create an instance of (open) Excel. You would use the reference to Excel (instead of Application) to execute the Excel VBA that would create the module and import the BAS file.

[bcn1988]

Hey steve,

I tried what you said, however there was no code that was recorded when I tried importing .BAS file into VBE. Regardless, this isn't the problem. I need the code for access VBA to open the VBE for Excel and import the .BAS into Excel VBE. Somewhere in my one line code (see below) is this issue:

Application.VBE.ActiveVBProject.VBComponents.Impor t (FileDirectory)

What is wrong with this coding and why is the module being imported into Access VBE?

[ssanfu]

Is there really a space in ".Impor t"

As far as I can tell, "Application" is referring to Access. That is why I suggested creating an Excel object (in Access VBA code). Something like this.. I cut this example code From Ken Snell's site:

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile as String, strTable as String
Dim strPassword As String

' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set objExcel = CreateObject("Excel.Application")
      blnEXCEL = True
Err.Clear
On Error GoTo 
End If
.
.
.

objExcel.VBE.ActiveVBProject.VBComponents.Import (FileDirectory)

To control Excel through automation, you have to reference Excel.

I know this won't work, but you see where I am headed???