-
Aug 7th, 2008, 04:49 PM
#1
Thread Starter
New Member
Excel VBA Issue
Ok, I am pretty lost right now. I need to have an excel macro open an "open file" dialog and then allow the user to select the file. I then need it to copy the data from that file and paste it into sheet1 and close the file that was just opened. The file that needs to be opened is a txt (tab delimited file) and. I simply need the data from it to be placed on the activesheet (sheet1) and then the file closed to be out of the way............
I am beating my head into a wall on this one and I know it should be fairly simple. Any help would be appreciated.
-
Aug 7th, 2008, 10:29 PM
#2
Re: Excel VBA Issue
try searching in the forum for tab delimited this should (i hope)find some code for opening a tab limited file,
you can also try recording macros for each step you want to perform, then join the codes together, to automate what you want to achieve
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 8th, 2008, 08:31 AM
#3
Re: Excel VBA Issue
Welcome to the forums.
I did Tools/Macro/Record New Macro
Then, I manually went through the process of importing a tab delimited text file. I stopped the macro recording, and this was the result
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/8/2008 by Hack
'
With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\Test.txt", _
Destination:=Range("A1"))
.Name = "Test"
.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 = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
You can try what I have, but I would suggest recording your own macro, importing one manually, stop the recording, and see what you wind up with.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|