Running a Module

[dariomarinmd]

I am sure this question has been asked a million times. One of the
participants in this group was gracious enough to send me code that
performs a task that I want to do in Access.

I created a new database and copied the code into a new module.

Now, what do I do?

I assume that I have to write a macro to run the module. Can someone
walk me through the process to create a simple macro that opens and
runs the module?

I am using both versions Access 2007 (at home) and Access 2003 (at
work)

I VERY MUCH appreciate your help.

 

[Tom van Stiphout]

On Wed, 29 Apr 2009 20:20:06 -0700 (PDT), (e-mail address removed)
wrote:

You can't "run a module". But you can run procedures in that module.
If you have a PUBLIC FUNCTION in that module, you can create a macro
and use the RunCode action.

-Tom.
Microsoft Access MVP

 

[dariomarinmd]

You can't "run a module". But you can run procedures in that module.
If you have a PUBLIC FUNCTION in that module, you can create a macro
and use the RunCode action.

-Tom.
Microsoft Access MVP









- Show quoted text -

Hi Tom, thanks for your response. Let me be more specific. My goal is
to import every excel file in a directory. I created a module and
copied the below code that someone from this group sent me into that
module and saved it.

Now what do I do? How do I run this? What do I do to make this work As
you might guess, I do not know visual basic. Thanks so much for your
help.

Option Compare Database

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "D:\Work\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Final Table"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

 

[OMAR]

nooooooooooo hablo ingles si no español........o.k

 

[Michael J. Strickland]

Looks like you need to name the routine (i.e. wrap the code you posted
inside a
"Public Sub ImportExcelFiles" statement and an "End Sub" statement.

From the top menu, select Debug-> Compile.

Then position your cursor after the "Public Sub ImportExcelFiles" line
and hit F5.

I don't recall exactly how, but you can attach this routine to a macro
(& hotkey) if you want.



--
Mike

-----------------------------------------------------------------------
Michael J. Strickland
Quality Services (e-mail address removed)
703-560-7380
-----------------------------------------------------------------------

You can't "run a module". But you can run procedures in that module.
If you have a PUBLIC FUNCTION in that module, you can create a macro
and use the RunCode action.

-Tom.
Microsoft Access MVP









- Show quoted text -

Hi Tom, thanks for your response. Let me be more specific. My goal is
to import every excel file in a directory. I created a module and
copied the below code that someone from this group sent me into that
module and saved it.

Now what do I do? How do I run this? What do I do to make this work As
you might guess, I do not know visual basic. Thanks so much for your
help.

Option Compare Database

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "D:\Work\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Final Table"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop