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 36
Rob HensonFinance AnalystAsked:
Who is Participating?
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
0
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
0
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

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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
0

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
0
Rob HensonFinance AnalystAuthor Commented:
Thank you! I was trying Enable, Disable and all sorts.
0
Rob HensonFinance AnalystAuthor Commented:
Chose own solution as found a way round different to proposed solution.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.