INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

run excel macro from access form

run excel macro from access form

(OP)
I get a csv file from a provider that won't import correctly into an access table. Two of the columns contain a 10 digit phone number. When I try to import the file these two columns don't import. I have written an excel macro to add single quote marks to each side of the phone number making the column text. Now when I import it all works correctly.

In my process I edit the csv file in excel and save the copy to the same name each month. This name is "ipwork.csv". I have an excel macro that will take care of the file editing to add the single quotes.

How can I add the language to my access Form VBA attached to an access form button to take care of running this excel macro, saving the excel file as the same name (ipwork.csv). I can then import the file to an access table.

Thanks in advance for any help you can provide.

Ron

RE: run excel macro from access form

Did you try this approach? smile

Your 2 fields for the phone numbers are defined as Text in your DB.
You will not do any calculation with them, but if you define them as Number, your data transfer would work just fine since there are no 10 digits phone numbers that would start with 0

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: run excel macro from access form

(OP)
This is true, the filed is marked as text, that is what I want. I need to be able to make them look better like "(123) 456-4890".

I still need to import the data. Marking them as text with quote marks works.

I need to know how to run an excel macro or some other method to make the numbers text.

Ron--

RE: run excel macro from access form

If you kept your phones as Number, you can simply do this:

Dim dbl As Double
dbl = 8005551212
Debug.Print Format(dbl, "(###) ###-####")

and you get:
(800) 555-1212

Just saying... smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: run excel macro from access form

(OP)
What you said made sense. I tried making the fields a number field. I tried double and long integer, neither worked.

I have attached the file to this message so you can play with it and see if you can make it work.

There are actually three fields that contain a 10 digit phone number. I only care about [DID Number] and [Source ID].

Ron--

RE: run excel macro from access form

"I have attached the file to this message" - no attached file found sad

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: run excel macro from access form

You have just 19 fields in your csv file
If that would be my task to deal with this CSV file, I would create my own 'csv to access table' code and add my own ' around whatever I want:

CODE

Dim strTextLine As String
Dim ary() As String
dim str As Sting

Open "C:\SomeFolder\ipwork.csv" For Input As #1
Do While Not EOF(1)
   Line Input #1, strTextLine   
   ary = Split(strTextLine, ",")

   str = "INSERT INTO MyTable (FieldA, FieldB, ...) " _
    & " VALUES( " & ary(0) & ", " & ary(1) & ", " & ary(2) & ", '" & ary(3) & "', ..."
    Execute str
Loop
Close #1 

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close