Conversion of CSV upload

Hi all,

Basics of situation:

Download from Bank Account creates CSV file,
Upload to Accounting system requires CSV but with different layout to Bank file.

I have got most of the process sorted but coming across issues.

Process Steps:
1) Import CSV data
2) Re-arrange layout
3) Export to CSV

Step 1 is where I am having issues.
Option 1 - I set it so that the Workings file would open the CSV file, select all and then copy into working file. This worked except when it came to dates. I am in UK so have date format dd/mm/yyyy but when I copy 10/04/2015 (10 April 2015) from the CSV and paste it into the working file it gets converted to 04/10/2015 (4 October 2015). Tried with  making destination cell text or general format and neither worked. Dates such as 14/04/2015 (14 April 2015) gets pasted as text so stays OK.

Option 2 - I have tried to set up a Data Connection to the CSV file. I have set this so that the Date column comes in as text and that works fine but I then want to break the connection to the source file. I have tried recording the Connection break routine but when I include it in
the script it errors out.

Files attached with obfuscated data.

Any help greatly appreciated.

Thanks
Rob H

PS I am going to be offline for a few days after this afternoon so apologise if I don't respond to queries promptly.
Dummy-Bank-Extract-v2.xlsm
Dummy-Extract-150716.csv
LVL 37
Rob HensonFinance AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls change datatype in the array to 4

and delete the "_" underscore of your Connectionstring

Regards
Rob HensonFinance AnalystAuthor Commented:
Changing the seventh entry which represents date column in the array to 4 changes the import of the date so that it is a date rather than text. That wasn't really an issue anyway.

Removing the "_1" from the ConnectionName string doesn't seem to do anything. I had included that because I noted each time I refreshed/reran the macro it added _# to the end of the Query and Connection Names.

The file is still linked to the source data. The source data will change each time the file is used so need to break the connection or just paste in raw data.

Thanks
Rob
Rgonzo1971Commented:
Why not simply use

    For Each cnn In ActiveWorkbook.Connections
        cnn.Delete
    Next

Open in new window

EDIT or the last one

If ActiveWorkbook.Connections.Count > 0 Then
    ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).Delete
End If

Open in new window

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Rob HensonFinance AnalystAuthor Commented:
Couldn't get either of those to work but I have found a way round the problem.

I am now inserting a sheet and creating the connection on that sheet. Then copy the data to my extract sheet and delete the sheet with the connection on.

However, when I delete the sheet, I would like to disable the warning message and can't recall the syntax for disabling warning messages.

Thanks
Rob

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
it's

Application.DisplayAlerts = False
'Your code
Application.DisplayAlerts = True
Rob HensonFinance AnalystAuthor Commented:
Thank you! I was trying Enable, Disable and all sorts.
Rob HensonFinance AnalystAuthor Commented:
Chose own solution as found a way round different to proposed solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.