Export VBA code to Excel File

[illini]

I have a dbs which will allow the user to export a query into an Excel file. After others have made changes to this file, the dbs will import it, and update the appropriate tables.

I have included vba code in the exported Excel file. (The MicroSoft Excel 9.0 Object Library must be referenced for this to work). When the excel file opens, it advises the user on which fields can be altered. The vba code includes a parameter which will allow the user to delete the code from the Excel file (so they don't have to look at the message everytime they open the file). Here's what I have so far.

Private Sub Cmd_Pull_Update_DEx_Click()
Form_Frm_Data_Export.Visible = False
If IsNull(COMPANY_ID) Then X = Format(Now, "yyyymmdd&quot Else X = COMPANY_ID
DoCmd.OutputTo acOutputQuery, "Qry_Data_Export", acFormatXLS, "C:\My Documents\" & X & "_DBS_Data.xls", False
qualifier = X & "_DBS_Data.xls"

Call Export_Excel_VBA

Y = "Successfully exported data as ..." & Chr(10) & "C:\My Documents\" & X & "_DBS_Data.xls"
MsgBox Y, , "DBS dbs - Data Export"
Form_Frm_Data_Export.Visible = True
End Sub

Sub Export_Excel_VBA()
Dim ExApp As Excel.Application

Set ExApp = New Excel.Application
ChDir "C:\My Documents\"
X = "Public X As String" & Chr(10) & _
"Sub auto_open()" & Chr(10) & _
"on error resume next" & Chr(10) & _
"Call msg" & Chr(10) & _
"If X = ""Y"" Then" & Chr(10) & _
" For i = 3 To 21" & Chr(10) & _
" Application.VBE.SelectedVBComponent.CodeModule.DeleteLines 3" & Chr(10) & _
" Next i" & Chr(10) & _
"End If" & Chr(10) & _
"End Sub" & Chr(10) & _
"Sub msg()" & Chr(10) & _
"If ActiveSheet.Name = ""Qry_Data_Export"" Then" & Chr(10) & _
" a = ""Welcome to the DBS Export File."" & Chr(10) & Chr(10) & _" & Chr(10) & _
" ""Please feel free to make any updates you wish;"" & Chr(10) & _" & Chr(10) & _
" ""however, please do not..."" & Chr(10) & _" & Chr(10) & _
" "" 1. Delete Row 1"" & Chr(10) & _" & Chr(10) & _
" "" 2. Alter the Rec_# field (Column A)"" & Chr(10) & Chr(10) & _" & Chr(10) & _
" ""If you wish to mark a record for deletion,"" & Chr(10) & _" & Chr(10) & _
" ""please place a 'XX' in the Rec_Type field (Column C)."" & Chr(10) & Chr(10) & _" & Chr(10) & _
" ""Do you wish to remove this message from this file?""" & Chr(10) & _
" If MsgBox(a, vbYesNo, ""DBS - Import / Export File"&quot = 6 Then X = ""Y""" & Chr(10) & _
"End If" & Chr(10) & _
"End Sub"

With ExApp
.Workbooks.Open FileName:=qualifier
.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule)
.VBE.CodePanes(1).CodeModule.AddFromString X
.ActiveWorkbook.Save
.Quit
End With
End Sub


My problem is that when 'Yes' is clicked on the msgbox, I receive an error. Once VBE is opened to debug, the code runs perfectly. I'm guessing that I need to include a line which opens VBE, but I haven't found the right line yet.

I would appreciate any input. Also, if there's a better way to export this vba code to Excel, please let me know.

Thanks in advance. -illini

 

[illini]

For those of you who may be interested, I was able to get this to work. Most of my colleagues use Excel. This VBA script is designed for an Access dbs which exports data to an Excel spreadsheet. The data can then be altered and imported back into Access. This sub actually exports a VBA module with the Excel spreadsheet. When the user opens the Excel file, they’ll get a MsgBox. If they click yes, the VBA code is deleted from the Excel file.

[tt]Private Sub Cmd_Pull_Update_DEx_Click()
Form_Frm_Data_Export.Visible = False
If IsNull(COMPANY_ID) Then X = Format(Now, "yyyymmdd&quot Else X = COMPANY_ID
DoCmd.OutputTo acOutputQuery, "Qry_Data_Export", acFormatXLS, "C:\My Documents\" & X & "_DBS_Data.xls", False
qualifier = X & "_DBS_Data.xls"

Call Export_Excel_VBA

Y = "Successfully exported data as ..." & Chr(10) & "C:\My Documents\" & X & "_DBS_Data.xls"
MsgBox Y, , "DBS dbs - Data Export"
Form_Frm_Data_Export.Visible = True
End Sub

Sub Export_Excel_VBA()
Dim ExApp As Excel.Application

Set ExApp = New Excel.Application
ChDir "C:\My Documents\"
X = "Public X As String" & Chr(10) & _
"Sub auto_open()" & Chr(10) & _
"on error resume next" & Chr(10) & _
"Call msg" & Chr(10) & _
"If X = ""Y"" Then" & Chr(10) & _
" For Each prj In Application.VBE.VBProjects" & Chr(10) & _
" y=y+1" & Chr(10) & _
" If Application.VBE.VBProjects.Name = ""Exported_VBA_"" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Then" & Chr(10) & _
" Application.VBE.VBProjects.VBComponents.Item(1).CodeModule.DeleteLines 1, 27" & Chr(10) & _
" MsgBox ""Save File Now."", , ""DBS - Import / Export File""" & Chr(10) & _
" End If" & Chr(10) & _
" Next prj" & Chr(10) & _
"End If" & Chr(10) & "End Sub" & Chr(10) & _
"Sub msg()" & Chr(10) & _
"If ActiveSheet.Name = ""Qry_Data_Export"" Then" & Chr(10) & _
" a = ""Welcome to the DBS Export File."" & Chr(10) & Chr(10) & _" & Chr(10) & _
" ""Please feel free to make any updates you wish; however if you intend"" & Chr(10) & _" & Chr(10) & _
" ""to import this datasheet back to the DBS, please do not..."" & Chr(10) & _" & Chr(10) & _
" "" 1. Delete Row 1"" & Chr(10) & _" & Chr(10) & _
" "" 2. Alter the Rec_# field (Column A)"" & Chr(10) & Chr(10) & _" & Chr(10) & _
" ""If you wish to mark a record for deletion,"" & Chr(10) & _" & Chr(10) & _
" ""please place a 'XX' in the Port_Type field (Column C)."" & Chr(10) & Chr(10) & _" & Chr(10) & _
" ""Do you wish to remove this message from this file?""" & Chr(10) & _
" If MsgBox(a, vbYesNo, ""DBS - Import / Export File"&quot = 6 Then X = ""Y""" & Chr(10) & _
"End If" & Chr(10) & "End Sub"
With ExApp
.Workbooks.Open FileName:=qualifier
.ActiveWorkbook.VBProject.Name = "Exported_VBA_" & Left(qualifier, Len(qualifier) - 4)
.VBE.ActiveVBProject.VBComponents.Add (vbext_ct_StdModule)
.VBE.CodePanes(1).CodeModule.AddFromString X
.ActiveWorkbook.Save
.Quit
End With
End Sub
[/tt] -illini

 

[MichaelRed]

Not to discuss the intent of the process, but I found it (even pasted into VB) difficult to read, and therefore suspect that it could be a maintenance / documentation / readability issue for the future. A possible alternative to the string concatenation via extension (which could also pose a problem) is presented below. Since I am a !Fan of excel, I did attempt to (with VERY minor modification) attempt to 'test' the thought through execution. Unfortunatly, a few items do not work for me:

within [sub Export_Excel], the .vbproject.name (initially) gave me a problem with the 'additional' part of the name not being enclosed in quotes (for my testing, I FOOLISHILY used a 'name' with embeded spaces).

Having overcome this, The VERY next line doesn't appear to appreciate the (enumerated constant?) "vbext_ct_StdModule".

I would like to persue the process, however this entire process (when it "crashes&quot leave the created .XLS object in a hard to access (and even more difficult to DELETE!) mode. I THINK the fact that the Ms. A. VBA moduls doesn't properly close the workbook (when it crashes) is the reason I have trouble deleting (or even accessing) the worksheet.

If I could get SOME feedback on these issues, I would (again) attempt to test the simplification and report on any 'successful' implementation.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over