+ Reply to Thread
Results 1 to 11 of 11

Reference Text File To Import Using Generic Letter or Number

  1. #1
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Reference Text File To Import Using Generic Letter or Number

    Hi all,

    I have recorded some script in excel to import a *.dat file, delete the first 19 rows of data, then export the data as a *.csv file.

    My problem is I have circa 30 *.dat files to import, all of which have large file names and I am fed up with copying and pasting them in.

    So my question is, in the below code, can I somehow use a reference at the start such as:

    abc1.dat = "1"
    abc2.dat = "2"

    Then where the files were used, reference the "1".dat and its exported version "1".csv for the abc1.dat case.

    Please Login or Register  to view this content.
    Appologies as I am somewhat *poor* at VBA code - hence my use of the Recorder.

    Cheers
    Last edited by VBA Noob; 01-26-2009 at 05:59 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello R_S_6,

    This version of your macro allows you to insert the file names into an array. Here the names are hard coded into the macro. The file names could be read from a sheet in the macro is running in. I would need to know what the sheet name is and the available column to have the macro read the file names in automatically.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Hi Leith, thanks for your reply.

    If I were to call the sheet in which the file names are stored 'File_Names' and the file names were listed in Column A starting at Row 1...

    Can you show me the required alterations to your piece of code?

    Thanks Again!

    Cheers
    Last edited by R_S_6; 01-26-2009 at 04:46 AM.

  4. #4
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Just had a go with the code you provided. (Although I prefer your idea of the file names in a sheet!)

    I had to modify this line:

    Please Login or Register  to view this content.
    to:
    Please Login or Register  to view this content.
    As it came up with an error here (Compile Error).

    After that it hit the following error at line:
    Please Login or Register  to view this content.
    Saying 'Compile Error: Sub or Function not defined' Not a clue on this one!

    Thought it would be good to highlight the above before hopefully you manage to alter the code to read in the file names from 'Column A' of Sheet 'File_Names'.

    Thanks Again!

  5. #5
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Question Re: Reference Text File To Import Using Generic Letter or Number

    Bump - Any Ideas

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Reference Text File To Import Using Generic Letter or Number

    Hello R_S_6,

    I have made the changes to the macro. Thanks for catching the typo. My typing skills are not very good. The file names are now read from the worksheet named "File_Names" column "A" starting with row 1 down to the last entry in the column.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

  7. #7
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    I've hit an issue on line:

    Please Login or Register  to view this content.
    It says:

    Run-time error '1004':

    Excel cannot find the text file to refresh this external data range

    Check to make sure the text file has not been moved or renamed, then try the refresh again


    I have double checked the folder extension to the test *.dat files, and also that the folder is present and correct to receive the converted *.dat files to *.csv files.

    Any ideas?

    Cheer Leith!

    Mark

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Reference Text File To Import Using Generic Letter or Number

    Hello Mark,

    Do you know how many files have been opened when the error occurs?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  9. #9
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Morning Leith,

    Just had another quick go at running the macro - fell over at the same point. Don't believe it has opened any files as yet, as none of the '*.dat' files have been written out in '*.csv' format in the destination folder 'D:\TEMP\X123\'.

    If you want to check the macro on your machine I have attached the 3 sample files, source folder and destination folder I am using as a test of the system/macro. Feel free to alter the destination folders etc as per a desired location on your machine.

    Also added test worksheet with labelled 'File_Names' sheet and listed file names to look up.

    Cheers

    Mark
    Attached Files Attached Files
    Last edited by R_S_6; 01-28-2009 at 06:40 AM.

  10. #10
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Bump bump

  11. #11
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Reference Text File To Import Using Generic Letter or Number

    Leith have you had any luck with this?

    Cheers,

    Mark

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1