Run a VBA function from a macro (Access 2003)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am writing a macro to automate importing data from an Excel spreadsheet
into a table when a button on the main form is pressed. I want to check to
see if the first record contains text after the import. If it does not, I
want to alert the user.

The problem is that the only way I know how to import data is with a macro,
and the only way I know how to do an IF-Then-Else structure is in VBA.

Any assistance would be greatly appreciated.
 
Here is help from Microsoft on the VB funciton to TransferSpreadsheet. This
will get you started.

-- TransferSpreadsheet Method
See AlsoApplies ToExampleSpecificsThe TransferSpreadsheet method carries out
the TransferSpreadsheet action in Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,
FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the
Applies To list.

TransferType Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.


SpreadsheetType Optional AcSpreadSheetType.

AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note You can link to data in a Lotus 1-2-3 spreadsheet file, but this data
is read-only in Microsoft Access. You can import from and link (read-only) to
Lotus .WK4 files, but you can't export Microsoft Access data to this
spreadsheet format. Microsoft Access also no longer supports importing,
exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0
spreadsheets by using this method.

If you leave this argument blank, the default constant
(acSpreadsheetTypeExcel8) is assumed.


TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to import spreadsheet data into, export
spreadsheet data from, or link spreadsheet data to, or the Microsoft Access
select query whose results you want to export to a spreadsheet.

FileName Optional Variant. A string expression that's the file name and
path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the
spreadsheet as field names when importing or linking. Use False (0) to treat
the first row of the spreadsheet as normal data. If you leave this argument
blank, the default (False) is assumed. When you export Microsoft Access table
or select query data to a spreadsheet, the field names are inserted into the
first row of the spreadsheet no matter what you enter for this argument.

Range Optional Variant. A string expression that's a valid range of cells
or the name of a range in the spreadsheet. This argument applies only to
importing. Leave this argument blank to import the entire spreadsheet. When
you export to a spreadsheet, you must leave this argument blank. If you enter
a range, the export will fail.

UseOA Optional Variant.

Remarks
For more information on how the action and its arguments work, see the
action topic.

You can leave an optional argument blank in the middle of the syntax, but
you must include the argument's comma. If you leave a trailing argument
blank, don't use a comma following the last argument you specify.


Note You can also use ActiveX Data Objects (ADO) to create a link by using
the ActiveConnection property for the Recordset object.


Example
The following example imports the data from the specified range of the Lotus
spreadsheet Newemps.wk3 into the Microsoft Access Employees table. It uses
the first row of the spreadsheet as field names.

DoCmd.TransferSpreadsheet acImport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"


Self taught user of Access 97 – 2003 with 7 years of experience with is part
of 20 years of overall database experience. I'm still learning.
 
Al,

I assume you are using a TransferSpreadsheet action in your macro. So
far, so good. As for alerting the user, this would be with a MsgBox
action in the macro. You don't need any If-Then-Else structure, I
imagine this will be a simple expression in the Condition column of the
macro design. (Note, if you can't see the Condition column by default,
you can select it from the View menu.) The specifics will depend on the
meaning of "if the first record contains text". First of all, how do
you know which record is the first record. But I expect that a DLookup
function would be applicable... please post back with more details,
maybe with an example, if you need more explicit help.
 
Wow! I never knew there was a condition column! That's not too surprising,
though, because I only started using macros in Access yesterday!

OK, about the first record containing text; the data imports, and it always
is inserted before all the data, in row one. I have an action "OpenTable",
then next I have "GoToRecord", and the arguments for that are, for Object
Type, Object Name, and Record, are Table, BCParts (the table name, of
course), and First, respectively. Now, if the user ran the program, and the
Excel sheet was empty, it would import blank data, and thus fill a row with
nothing. (I tried it.) I want to delete the empty row (IF it is empty, thus
my condition) and alert the user that the data was NOT imported, because it
wasn't there! Make sense? Now, how to word a condition?

Thank you for your help so far,
 
Al,

I would not do it like this. For one thing, in Access the First record
is a random record. It is not reliable to use for any kind of data
processing. The data in a table is not in any particular order, so
there is no such thing as first in fact, even though it may look like it!

So, here's the situation, apparently. If there is no data in the
spreadsheet, a blank record in imported into the Access table. If the
spreadsheet does have data, then the table will always end up with more
than one record, am I right? So that's what I would use. Forget the
OpenTable action and the GoToRecord action. First of all make a Delete
Query to delete all records from the BCParts table. Post back if you
can't figure out how to do this. Then, make your macro like this...
Condition: DCount("*","BCParts")=1
Action: MsgBox
Message: "No data imported"
Condition: ...
Action: SetWarnings
Warnings On: No
Condition: ...
Action: OpenQuery
Query Name: your delete query
 
All,

It's been a while since I've used this command but I did run into a
problem...

If memory serves, this command will APPEND data to a table (If the table
already exists). So of course that means every time you add data the table
grows. Not a problem, unless of course you are adding the same UPDATED
spreadsheet to your Access table.

One way to get around this is to write a function that deletes the table
prior to appending all of your excel sheets into your database (My data was
derived from 11 different spreadsheets all located in one folder -- The
investigators only had access to their own excel file...it worked
perfectly).

Just my 2 cents

Rob F
 
Back
Top