If you need prompts, and if the files are in different folders, then
Application.GetOpenFileName (my earlier code uses it) can be helpful
Regards,
Sam
On Wed, Oct 5, 2011 at 11:06 PM, Sam Mathai Chacko <samde...@gmail.com>wrote:
> Just take care of the sheet activation also. If you are going with the
> loop, then use Sheets("List" & i).activate
>
> Regards,
>
> Sam
>
>
> On Wed, Oct 5, 2011 at 10:32 PM, dguillett1 <dguille...@gmail.com> wrote:
>
>> That can be done but it's probably easier to know the folder you need and
>> modify my idea to suit
>> Sub GetDataSAS()
>>
>> 'DATA_20110101, DATA_20110201
>>
>> myfolder = "yourdriveletter:\**yourfoldername\" 'these variables could
>> be entered in a cell
>> startname = "DATA_20110"
>> endname = "01"
>> For i = 1 To 3
>> mystring = myfolder & startname & i & endname
>> MsgBox mystring
>> With ActiveSheet.QueryTables.Add(**Connection:="TEXT;" _
>> & mystring & ".csv", Destination:=Range("A1"))
>>
>> 'your stuff
>> 'copy needed range to other sheet
>>
>> next i
>> end sub
>>
>> -----Original Message----- From: Seba
>> Sent: Wednesday, October 05, 2011 10:51 AM
>>
>> To: MS EXCEL AND VBA MACROS
>> Subject: Re: $$Excel-Macros$$ prompting for files for data import
>>
>>
>> Thank you for your help, however, if I understand your suggestion
>> correctly, this would still open files from one and only location? In
>> my case, the source files are in different locations every month and
>> have different endings in their names: DATA_20110101, DATA_20110201,
>> etc...
>>
>> That is why I need to be prompted to select the files manually.
>>
>> On 5 okt., 16:56, "dguillett1" <dguille...@gmail.com> wrote:
>>
>>> The example you provide makes it fairly easy
>>>
>>> for i= 1 to 3
>>> Sheets("List" & 1).QueryTables.Add(Connection:**= _
>>> "TEXT;Path to my file" & I &".csv", Destination:= sheets("List" &
>>> 1).range("a1")
>>> 'etc
>>> next i
>>>
>>> However, you can probably do it with ONE query table using the variables
>>> and
>>> have your macro copy only the desired data elsewhere.'
>>> for i= 1 to 3
>>> Sheets("sourcesheet").**QueryTables.Add(Connection:= _
>>> "TEXT;Path to my file" & I &".csv", Destination:=
>>> sheets("sourcesheet).range("**a1")
>>> 'etc
>>> copy range("a2:z4") sheets("destinationsheet").**range("a1")
>>>
>>> next i
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Seba
>>> Sent: Wednesday, October 05, 2011 4:37 AM
>>> To: MS EXCEL AND VBA MACROS
>>> Subject: $$Excel-Macros$$ prompting for files for data import
>>>
>>> Hi all,
>>>
>>> I have to import data from 3 different files into 1 and format the
>>> data. Since everything is more or less fixed, I have recorded the
>>> macro below and removed the code for scrolling and such. The only
>>> problem for me is that each month the locations and filenames of the
>>> files to import are changing. What woudl the code be so the excel
>>> prompts me for each file?
>>>
>>> Thank you in advance for your help.
>>>
>>> Code:
>>>
>>> ------------------------------**------------------------------**
>>> ---------
>>> Sub uvoz_podatkov()
>>> '
>>> ' uvoz_podatkov Makro
>>> '
>>>
>>> '
>>> With ActiveSheet.QueryTables.Add(**Connection:= _
>>> "TEXT;Path to my file1.csv", Destination:= _
>>> Range("$A$1"))
>>> .Name = "MyFile1"
>>> .FieldNames = True
>>> .RowNumbers = False
>>> .FillAdjacentFormulas = False
>>> .PreserveFormatting = True
>>> .RefreshOnFileOpen = False
>>> .RefreshStyle = xlInsertDeleteCells
>>> .SavePassword = False
>>> .SaveData = True
>>> .AdjustColumnWidth = True
>>> .RefreshPeriod = 0
>>> .TextFilePromptOnRefresh = False
>>> .TextFilePlatform = 852
>>> .TextFileStartRow = 1
>>> .TextFileParseType = xlDelimited
>>> .TextFileTextQualifier = xlTextQualifierDoubleQuote
>>> .TextFileConsecutiveDelimiter = False
>>> .TextFileTabDelimiter = True
>>> .TextFileSemicolonDelimiter = False
>>> .TextFileCommaDelimiter = False
>>> .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)
>>> .TextFileTrailingMinusNumbers = True
>>> .Refresh BackgroundQuery:=False
>>> End With
>>>
>>> Sheets("List2").Select
>>> With ActiveSheet.QueryTables.Add(**Connection:= _
>>> "TEXT;Path to my file2.csv", Destination:= _
>>> Range("$A$1"))
>>> .Name = "MyFile2"
>>> .FieldNames = True
>>> .RowNumbers = False
>>> .FillAdjacentFormulas = False
>>> .PreserveFormatting = True
>>> .RefreshOnFileOpen = False
>>> .RefreshStyle = xlInsertDeleteCells
>>> .SavePassword = False
>>> .SaveData = True
>>> .AdjustColumnWidth = True
>>> .RefreshPeriod = 0
>>> .TextFilePromptOnRefresh = False
>>> .TextFilePlatform = 852
>>> .TextFileStartRow = 1
>>> .TextFileParseType = xlDelimited
>>> .TextFileTextQualifier = xlTextQualifierDoubleQuote
>>> .TextFileConsecutiveDelimiter = False
>>> .TextFileTabDelimiter = True
>>> .TextFileSemicolonDelimiter = False
>>> .TextFileCommaDelimiter = False
>>> .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)
>>> .TextFileTrailingMinusNumbers = True
>>> .Refresh BackgroundQuery:=False
>>> End With
>>>
>>> Sheets("List3").Select
>>> With ActiveSheet.QueryTables.Add(**Connection:= _
>>> "TEXT;Path to my file3.csv", Destination:= _
>>> Range("$A$1"))
>>> .Name = "MyFile3"
>>> .FieldNames = True
>>> .RowNumbers = False
>>> .FillAdjacentFormulas = False
>>> .PreserveFormatting = True
>>> .RefreshOnFileOpen = False
>>> .RefreshStyle = xlInsertDeleteCells
>>> .SavePassword = False
>>> .SaveData = True
>>> .AdjustColumnWidth = True
>>> .RefreshPeriod = 0
>>> .TextFilePromptOnRefresh = False
>>> .TextFilePlatform = 852
>>> .TextFileStartRow = 1
>>> .TextFileParseType = xlDelimited
>>> .TextFileTextQualifier = xlTextQualifierDoubleQuote
>>> .TextFileConsecutiveDelimiter = False
>>> .TextFileTabDelimiter = True
>>> .TextFileSemicolonDelimiter = False
>>> .TextFileCommaDelimiter = False
>>> .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)
>>> .TextFileTrailingMinusNumbers = True
>>> .Refresh BackgroundQuery:=False
>>> End With
>>>
>>> Range("A:I,K:L").Select
>>> Range("K1").Activate
>>>
>>> Range("A:I,K:L,O:O,R:R,T:T,V:**W,Y:Z").Select
>>> Range("Y1").Activate
>>>
>>> Range("A:I,K:L,O:O,R:R,T:T,V:**W,Y:Z,AB:AC,AE:AF,AH:AI").**Select
>>> Range("AH1").Activate
>>>
>>> Range("A:I,K:L,O:O,R:R,T:T,V:**W,Y:Z,AB:AC,AE:AF,AH:AI,AK:AN"**).Select
>>> Range("AK1").Activate
>>> Selection.Delete Shift:=xlToLeft
>>>
>>> Rows("1:1").Select
>>> Selection.AutoFilter
>>> Range("D:D,A:A").Select
>>> Range("A1").Activate
>>> With Selection.Interior
>>> .Pattern = xlSolid
>>> .PatternColorIndex = xlAutomatic
>>> .ThemeColor = xlThemeColorAccent2
>>> .TintAndShade = 0.599993896298105
>>> .PatternTintAndShade = 0
>>> End With
>>>
>>> Sheets("List2").Select
>>> Columns("A:I").Select
>>>
>>> Range("A:I,N:N,R:R,T:U").**Select
>>> Range("T1").Activate
>>>
>>> Range("A:I,N:N,R:R,T:U,W:X,Z:**AA,AC:AD").Select
>>> Range("AC1").Activate
>>>
>>> Range("A:I,N:N,R:R,T:U,W:X,Z:**AA,AC:AD,AF:AG,AI:AJ").Select
>>> Range("AI1").Activate
>>>
>>> Range("A:I,N:N,R:R,T:U,W:X,Z:**AA,AC:AD,AF:AG,AI:AJ,AL:AM,AO:**
>>> AP,AR:AS").Select
>>> Range("AR1").Activate
>>> ActiveWindow.ScrollColumn = 36
>>>
>>> Range("A:I,N:N,R:R,T:U,W:X,Z:**AA,AC:AD,AF:AG,AI:AJ,AL:AM,AO:**
>>> AP,AR:AS,AT:AU").
>>> _
>>> Select
>>> Range("AT1").Activate
>>> Selection.Delete Shift:=xlToLeft
>>>
>>> Rows("1:1").Select
>>> Selection.AutoFilter
>>> Range("E:E,A:A").Select
>>> Range("A1").Activate
>>> With Selection.Interior
>>> .Pattern = xlSolid
>>> .PatternColorIndex = xlAutomatic
>>> .ThemeColor = xlThemeColorAccent2
>>> .TintAndShade = 0.599993896298105
>>> .PatternTintAndShade = 0
>>> End With
>>>
>>> Sheets("List1").Select
>>> Range("A:B,C:C,D:I,K:K,N:N").**Select
>>> Range("N1").Activate
>>>
>>> Range("A:B,C:C,D:I,K:K,N:N,Q:**T").Select
>>> Range("Q1").Activate
>>>
>>> Range("A:B,C:C,D:I,K:K,N:N,Q:**T,W:W,Y:Z,AB:AC,AE:AF").Select
>>> Range("AE1").Activate
>>>
>>> Range("A:B,C:C,D:I,K:K,N:N,Q:**T,W:W,Y:Z,AB:AC,AE:AF,AH:AI,**
>>> AK:AL,AN:AO,AQ:AR").
>>> _
>>> Select
>>> Range("AQ1").Activate
>>>
>>> Range( _
>>>
>>> "A:B,C:C,D:I,K:K,N:N,Q:T,W:W,**Y:Z,AB:AC,AE:AF,AH:AI,AK:AL,**
>>> AN:AO,AQ:AR,AT:AX").
>>> _
>>> Select
>>> Range("AT1").Activate
>>> Selection.Delete Shift:=xlToLeft
>>>
>>> Rows("1:1").Select
>>> Selection.AutoFilter
>>> Range("D:D,A:A").Select
>>> Range("A1").Activate
>>> With Selection.Interior
>>> .Pattern = xlSolid
>>> .PatternColorIndex = xlAutomatic
>>> .ThemeColor = xlThemeColorAccent2
>>> .TintAndShade = 0.599993896298105
>>> .PatternTintAndShade = 0
>>> End With
>>> End Sub
>>> ------------------------------**------------------------------**
>>> -------------------------
>>>
>>> --
>>> ------------------------------**------------------------------**
>>> ----------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :http://twitter.com/**
>>> exceldailytip <http://twitter.com/exceldailytip>
>>> 2. Join our LinkedIN group
>>> @http://www.linkedin.com/**groups?gid=1871310<http://www.linkedin.com/groups?gid=1871310>
>>> 3. Excel tutorials
>>> athttp://www.excel-macros.**blogspot.com<http://www.excel-macros.blogspot.com>
>>> 4. Learn VBA Macros
>>> athttp://www.quickvba.**blogspot.com<http://www.quickvba.blogspot.com>
>>> 5. Excel Tips and Tricks
>>> athttp://exceldailytip.**blogspot.com<http://exceldailytip.blogspot.com>
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><>**<><><><><><><>
>>> Like our page on facebook , Just follow below linkhttp://
>>> www.facebook.com/**discussexcel <http://www.facebook.com/discussexcel>
>>>
>>
>> --
>> ------------------------------**------------------------------**
>> ----------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/**
>> exceldailytip <http://twitter.com/exceldailytip>
>> 2. Join our LinkedIN group @
>> http://www.linkedin.com/**groups?gid=1871310<http://www.linkedin.com/groups?gid=1871310>
>> 3. Excel tutorials at
>> http://www.excel-macros.**blogspot.com<http://www.excel-macros.blogspot.com>
>> 4. Learn VBA Macros at
>> http://www.quickvba.blogspot.**com<http://www.quickvba.blogspot.com>
>> 5. Excel Tips and Tricks at
>> http://exceldailytip.blogspot.**com<http://exceldailytip.blogspot.com>
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><>**<><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/**discussexcel<http://www.facebook.com/discussexcel>
>> --
>> ------------------------------**------------------------------**
>> ----------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/**
>> exceldailytip <http://twitter.com/exceldailytip>
>> 2. Join our LinkedIN group @
>> http://www.linkedin.com/**groups?gid=1871310<http://www.linkedin.com/groups?gid=1871310>
>> 3. Excel tutorials at
>> http://www.excel-macros.**blogspot.com<http://www.excel-macros.blogspot.com>
>> 4. Learn VBA Macros at
>> http://www.quickvba.blogspot.**com<http://www.quickvba.blogspot.com>
>> 5. Excel Tips and Tricks at
>> http://exceldailytip.blogspot.**com<http://exceldailytip.blogspot.com>
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><>**<><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/**discussexcel<http://www.facebook.com/discussexcel>
>>
>
>
>
> --
> Sam Mathai Chacko
>
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
