Results 1 to 1 of 1

Code to open fixed with file file with variable name

Threaded View

jlax34 Code to open fixed with file... 03-03-2015, 11:49 PM
  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    84

    Code to open fixed with file file with variable name

    I need to open an excel file that is not in an excel format and requires "fixed width" parameters to open correctly. Initially I had this written to open the specific file just for myself, but am looking to modify this so that anyone can use for any file location.

    Original Code:

    Workbooks.OpenText Filename:= _
            "C:\Users\a614889\Documents\One time Reports\xnet", Origin:=437, StartRow:= _
            1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9), Array(7, 1 _
            ), Array(13, 9), Array(15, 1), Array(18, 9), Array(19, 1), Array(25, 9), Array(32, 1), Array _
            (63, 1), Array(69, 9), Array(72, 1), Array(73, 9), Array(76, 1), Array(82, 9), Array(101, 1) _
            ), TrailingMinusNumbers:=True
    Now, I am adding this to prompt the user to select the location they have their file saved

    Dim fNameAndPath As Variant
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Workbooks.Open Filename:=fNameAndPath
    Where I am struggling, is what the syntax is to open the file with the fixed with settings. I tried this and get a compile error on StartRow. I know sytnax can get iffy when broken into lines and tried moving some of it up a line and breaking in a different spot, but always get the compile error.

    Dim fNameAndPath As Variant
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Workbooks.Open Filename:=fNameAndPath, Origin:=437, StartRow:= _
            1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(5, 9), Array(7, 1 _
            ), Array(13, 9), Array(15, 1), Array(18, 9), Array(19, 1), Array(25, 9), Array(32, 1), Array _
            (63, 1), Array(69, 9), Array(72, 1), Array(73, 9), Array(76, 1), Array(82, 9), Array(101, 1) _
            ), TrailingMinusNumbers:=True
    Last edited by jlax34; 03-03-2015 at 11:52 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro - Master file to import data from another open file with variable file name
    By jdodz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2012, 10:56 PM
  2. [SOLVED] Open file from a folder and assign that file to an Object variable
    By pratyu in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-22-2012, 02:11 AM
  3. VBA Changing a fixed file path with open file dialog box
    By Dan27 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-01-2011, 08:54 AM
  4. VBA for excel 2003. Open file dialog box, open 2007 file xlsx, continue with code
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2011, 12:12 AM
  5. VBA code to import text file to the fixed excel file
    By wangdian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2010, 10:13 AM

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