Jump to content
MSFN is made available via donations, subscriptions and advertising revenue. The use of ad-blocking software hurts the site. Please disable ad-blocking software or set an exception for MSFN. ×

Recommended Posts

I'm trying to make an Excel macro to import a txt file using the text import wizard with hard-coded fixed-width values (users would be opening identically formatted files into the spreadsheet).

I've gotten this far, but I need to figure out how to make excel prompt for the filename instead of it being whatever file I open when I created the macro. From below, I don't want it to always open C:\something.txt, but rather prompt for a filename each time.

	With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\something.txt" _
, Destination:=Range("A16"))
.Name = "[logs] 2M6-04032007-084243"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 28
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(9, 15, 11, 11, 18, 15)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...