VBA code for creating New Data Source for MS Text Drive (TXT./CSV) on users computers w/o setting up each computer?

[Margie Burgett]

Hello Experts,

I have over 400 report customers that are running Excel2003 and in the next few months Excel2007.

In order to support the migration from .XLS data sources (due to row limit) to CSV data sources using in our Excel pivot reports, I need to locate code that can either bypass the requirement to create a New Data Source for the Microsoft Text Driver (txt,csv) on everyones computer or code to execute to have this new data source defined.

At least that is what I am seeing from a test user case. They will receive an error and the only way that I have found to work around it is to step them through creating a new data source on their computer. Such as using the steps: Windows Start Menu -> Run -> odbcad32 found on this site

One more question... I have an Excel2003 VBA macro that is now erroring out in a customers Excel2007. Any direction as where I could go to get assisting in finding out what the issue is? Just remember, I am very new to all this but don't mind spending what ever time is needed.

Can anyone help?

Thanks, Margie.

 

[xenou]

I may be wrong but I believe:
1) you can create your dsn file and save it
2) you could copy that file to other user's computers - just provide the instructions on where to save it.
3) with text files you may need to provide a schema.ini file as well which is saved in the same directory as the Excel workbook - if you get one in the same directory as the Excel workbook when you create the dsn file, then you can assume you need it!
4) then your user's could just edit the query and select the new dsn.

HTH

With your 2007 macro issues, it comes down to specifics. What's not working.

 

[Margie Burgett]

Hello Alexander,

The CSV files reside out on a SharePoint site.

I have created a macro button in the Excel report that users will execute when they receive an email notification that the CSV file is refreshed with new data. This macro copies the CSV file from SharePoint and saves it into C:\Reports\CSV_Files\ folder.

In order for this macro to work in my testing, I require to create a new data source on that computer.

My other thought is that I could provide instructions for the users to create a New Data Source using the Windows Start Menu -> Run -> odbcad32 on their computer but I need to make this all transparant to them. Too many users to support by one person.

I just had a thought that if I ZIP the CSV file for the SharePoint download process then have the users unZIP it to a folder on their computer named C:\Reports\CSV_Files\ then possibly this would work? I'll give it a test this week

Do you by chance know the VB code for have a folder created such as C:\Reports\CSV_Files\ on a computer if it is not already available? If it is there, then this step would just be bypassed. If it isn't then the users would receive a msg that this folder has been created.

As far as the macro issue, we have isolated it to a couple worksheets contained in an excel workbook. It looks like not to be related to the Excel2007 application though. It has been an interesting journey so far.

Thank you for your response.

Margie.

 

[xenou]

Here's the folder code you can use:

Sub TEST()
    Call MyCSVFolder
End Sub
'-------------------
Sub MyCSVFolder()
Dim oFileSystem As Object
Dim msg As String

Set oFileSystem = CreateObject("Scripting.FileSystemObject")

With oFileSystem
    If Not .FolderExists("C:\Reports") Then
        .CreateFolder ("C:\Reports")
    End If
    If Not .FolderExists("C:\Reports\CSV_Files") Then
        .CreateFolder ("C:\Reports\CSV_Files")
        msg = "A new folder has been created on your computer:" & vbNewLine & vbNewLine
        msg = msg & "C:\Reports\CSV_Files"
        MsgBox Prompt:=msg, Buttons:=vbInformation, Title:="New Folder"
    End If
End With

End Sub

Let us know how you are coming along if there are any more questions. AB

 

[Margie Burgett]

Hello Alexander,

Thank you very much for the code. I am still very much a newbie to all this and obtaining this allows me to decipher it and learn along with the books that I have.

I'll let you know how it goes in the next couple of weeks.

If you happen to find anything out about mitigating the requirement to create a new data source on a persons computer for MS Text CSV/TXT, please let me know.

As I mention I am having them pull the CSV data source file from a SharePoint site therefor not 'pushing' it to them. I still require to test out Zipping the file with WinZip and seeing what I can do there.

Goal is to:
1. Integrate your code to create the target folder location.
2. Test downloading and then unZipping the CSV file to this folder without to much user intervention.... I think I may have it.

Thank you, Margie.