run excel macro from access form

[ronwmon]

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

 

[Andrzejek]

Did you try this approach?

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.

 

[ronwmon]

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--

 

[Andrzejek]

If you kept your phones as Number, you can simply do this:
[tt]
Dim dbl As Double
dbl = 8005551212
Debug.Print Format(dbl, "(###) ###-####")
[/tt]
and you get:
[tt](800) 555-1212[/tt]

Just saying...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[ronwmon]

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--

 

[Andrzejek]

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[ronwmon]

I will try again to attach the file.

Ron--

 

[SkipVought]

FYI FAQ68-6659

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

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:

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) & ", [blue]'[/blue]" & ary(3) & "[blue]'[/blue], ..."
    Execute str
Loop
Close #1

Have fun.

---- Andy

There is a great need for a sarcasm font.