Help with VBA code in macro

[Cam]

Hello,

I created 2 append queries to append data to a table called "Output" and
would like to help in creating a macro to first clear all the existing data
in "Output" table, then run the 2 queries. After data is appended to the
table, copy new data from Output table to either sheet1 of the "BSchedule"
Excel file or refresh the data by importing from the Excel file. Thanks

 

[Ken Snell \(MVP\)]

Create a delete query that will delete all the data from the Output table.
Run that query in your code the same way you now run the append queries; but
run the delete query before you run the append queries.

 

[Ken Snell \(MVP\)]

Click the Show All Actions button in the Show/Hide group on the Macro Design
Tools tab.

 

[Cam]

Thanks Ken,

What about the macro to exporting the appended table data to the Excel file?
What VBA code or macro to execute that? Thanks

 

[Ken Snell \(MVP\)]

TransferSpreadsheet is the action to export data to an EXCEL file. However,
ACCESS will export the data to a sheet that is named the same as the query
or table that you use for the export. Although you cannot specify with 100%
reliability the sheet name to which you want to export the data, see this
article for suggestions on how the undocumented Range argument might be
helpful for you in this situation:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Cam]

Ken,

The TransferSpreadsheet worked great except some fields are exported as text
in Excel rather than number which is the actual field type on the table.

What can I do in the macro or function to tell it to export the actual data
field type as it is on the table? Thanks

 

[Ken Snell \(MVP\)]

You're exporting a table, not a query, right? If the table's field is
numeric datatype, then EXCEL should get the data as Number unless the data
are being written onto a worksheet that already contains data, and the
column that gets that field's data is already formatted as Text.

You could try creating a query that returns all the records from the table,
and relace the numeric field with a calculated field that "converts" the
data to a number:

FieldName: CLng(FieldName)

(CLng is example -- it converts to Long Integer. Other functions are CInt,
CSng, CDbl, etc.)
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/