Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any Idea on how to modify this code relating to query???
Dear All Please refer to the below code... Can anyone show me how I can modif the code so that I can get the file directory (in bold) to be referre to a specified cell? For eg, the text in Sheet1 Cell A1 i F:\work\job.dat. Hence, the macro should take the value of Cell A1 an then continue with its processes... One problem I encounter is that i says that the "Destination" should be the same as where the specifie cell is but I want the destination to be say Sheet2. I would reall appreciate any suggestion here... Cheers Sub test2() Range("A1").Select Wit ActiveSheet.QueryTables.Add(Connection:="TEXT;*F:\ work\job.dat*", _ Destination:=Sheets("Sheet3").Range("A1")) .Name = "job" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Sheets("Sheet3").Select End Su -- hc ----------------------------------------------------------------------- hce's Profile: http://www.excelforum.com/member.php...nfo&userid=351 View this thread: http://www.excelforum.com/showthread.php?threadid=26913 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Any Idea on how to modify this code relating to query???
Sub test2()
Dim fname As String fname = Sheets("Sheet1").Range("A1").Value 'stores the file name Sheets("Sheet3").Select 'assumes sheet3 exists already With ActiveSheet.QueryTables.Add(Connection:="TEXT;*& fname &*", _ Destination:=Range("A1")) Name = "job" .....Rest of query Mike F "hce" wrote in message ... Dear All Please refer to the below code... Can anyone show me how I can modify the code so that I can get the file directory (in bold) to be referred to a specified cell? For eg, the text in Sheet1 Cell A1 is F:\work\job.dat. Hence, the macro should take the value of Cell A1 and then continue with its processes... One problem I encounter is that it says that the "Destination" should be the same as where the specified cell is but I want the destination to be say Sheet2. I would really appreciate any suggestion here... Cheers Sub test2() Range("A1").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT;*F:\ work\job.dat*", _ Destination:=Sheets("Sheet3").Range("A1")) Name = "job" FieldNames = True RowNumbers = False FillAdjacentFormulas = False PreserveFormatting = True RefreshOnFileOpen = False RefreshStyle = xlInsertDeleteCells SavePassword = False SaveData = True AdjustColumnWidth = True RefreshPeriod = 0 TextFilePromptOnRefresh = False TextFilePlatform = 850 TextFileStartRow = 1 TextFileParseType = xlDelimited TextFileTextQualifier = xlTextQualifierDoubleQuote TextFileConsecutiveDelimiter = False TextFileTabDelimiter = True TextFileSemicolonDelimiter = False TextFileCommaDelimiter = True TextFileSpaceDelimiter = False TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) TextFileTrailingMinusNumbers = True Refresh BackgroundQuery:=False End With Sheets("Sheet3").Select End Sub -- hce ------------------------------------------------------------------------ hce's Profile: http://www.excelforum.com/member.php...fo&userid=3518 View this thread: http://www.excelforum.com/showthread...hreadid=269135 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify code | Excel Discussion (Misc queries) | |||
Modify Code | Excel Worksheet Functions | |||
Help to modify the query? | Excel Worksheet Functions | |||
Modify Web Query using macros? | Excel Programming | |||
How to modify VBA code for Add-in? | Excel Programming |