Inserting Excel VBA into an Excel File while Exporting

[DCPan]

Hey all,

I was wondering...while Exporting into an Excel file from Access, can we
actually insert a little bit of Excel VBA into the dropped file?

Thanks!

 

[Tom van Stiphout]

On Wed, 15 Oct 2008 16:33:01 -0700, DCPan

No.
But you can use Automation to manipulate the Excel file after the
fact. In Access you can use the Modules collection, and the Module
object and change code dynamically. I'm pretty sure Excel has a
similar mechanism.

-Tom.
Microsoft Access MVP

 

[DCPan]

Hi Tom,

Basically, I have an access database that compares spreadsheets from
multiple sources.

So, user presses a button, an open/save window pops up to allow user to
select the files to process, and the database drops a processed file with
another dialogue window.

Now, the user would like this to happen to one of the columns, just a simple
VBA that resize the column width if he changes anything.

Would what you are suggesting allow me to make this little sub module
populate on every downloaded worksheet?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Columns("A:A").Select
Selection.Columns.AutoFit

End If

If Target.Column = 2 Then

Columns("B:B").Select
Selection.Columns.AutoFit

End If

If Target.Column = 3 Then

Columns("C:C").Select
Selection.Columns.AutoFit

End If

End Sub