CODEWITHSUNDEEP
sqlexcelvbams-accessms-access-2010
msiudut
msiudut

Reputation: 251

Excel to Access, change data types as part of a button's on click action

I have 2 reports I run in SAP (I only have user level permissions) twice daily that need to end up in Access. The current process looks something like this:

(1) Run report one, select the fields I need, export to Excel.
(2) Run a saved import within Access to get the table.
(3) Switch that table to design view so I can edit the data types to sync with the table I intend to update.
(4) Run an append query. 
(5) Rinse and repeat for report 2.

Since I'm looking to delegate this task to a user I'd like a button that changes the process to:

(1) Run report one, select the fields I need, export to Excel.
(2) Click the "import, change data type and append" button. 
(3) Rinse and repeat for report 2.

The Excel document's formatting looks like this:

txtField1 txtField2 txtField3 txtField4 txtField5 DateAndTimeField1

It also uses different naming conventions than Access.

Access needs:

TxtField1 txtField2 NumberField1 NumberField2 NumberField3 DateAndTimeField1

I can get the Excel document into Access using a macro then just add an append query to the button's actions. But I can't append it to the table I want unless the data types sync.

How do I switch the data types and (if using vba) column headings on the imported document as part of the button's on click action?

Upvotes: 0

Views: 51

Answers (1)

Constuntine
Constuntine

Reputation: 508

Maybe try Range("A1").NumberFormat Or, Range("D2").Value = Val(Range("C2").Value) The Val() function.

Upvotes: 0

Related Questions