Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
How do I import a file to Excel with a set of specifications? I don't want
to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
Saved from a previous post:
Start a new workbook Start recording a macro that will be stored in this macro workbook. File|Open your text file that needs to be imported. Format it the way you like. Insert columns, add headers, freeze panes, widen columns, add filters, do the page setup--everything you can think of. When you're done, save that workbook with the macro. I like to put a big button from the Forms toolbar on the only worksheet in that macro workbook and assign this macro to that big button. I'll add a few instructions to that sheet, too. If the file name to open is always the same and in the same location, then I'm about done. If the location or file name changes, I'll tweak the code to ask for the file. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Ripper wrote: How do I import a file to Excel with a set of specifications? I don't want to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
Dave,
How do I modify/add to this code so that once the text file has been opened I can go back and forth to it not knowing the name of the file until after it's been selected? ie I think I need to declare a different variable for the name of the file I just opened. The "myFileName" refers to the full path and I think I just want the name of the file. eg in my code I have Windows("SupporterStats.txt").Activate But I want to use the code, below, to open any text file and then run some more code, before acting again on the unknown name text file. Rob "Dave Peterson" wrote in message ... Saved from a previous post: Start a new workbook Start recording a macro that will be stored in this macro workbook. File|Open your text file that needs to be imported. Format it the way you like. Insert columns, add headers, freeze panes, widen columns, add filters, do the page setup--everything you can think of. When you're done, save that workbook with the macro. I like to put a big button from the Forms toolbar on the only worksheet in that macro workbook and assign this macro to that big button. I'll add a few instructions to that sheet, too. If the file name to open is always the same and in the same location, then I'm about done. If the location or file name changes, I'll tweak the code to ask for the file. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Ripper wrote: How do I import a file to Excel with a set of specifications? I don't want to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
I'd stay away from using the filename and I'd stay away from using the windows
collection, too. You can make a couple more variables that represent the worksheet and workbook that holds the imported data. Option Explicit Sub Testme01() Dim myFileName As Variant dim wks as worksheet dim wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! 'while the .txt file is the activesheet set wks = activesheet set wkbk = activeworkbook 'or wks.parent 'then you can use: with wks .range("a1").value = "hi there" end with End Sub RobN wrote: Dave, How do I modify/add to this code so that once the text file has been opened I can go back and forth to it not knowing the name of the file until after it's been selected? ie I think I need to declare a different variable for the name of the file I just opened. The "myFileName" refers to the full path and I think I just want the name of the file. eg in my code I have Windows("SupporterStats.txt").Activate But I want to use the code, below, to open any text file and then run some more code, before acting again on the unknown name text file. Rob "Dave Peterson" wrote in message ... Saved from a previous post: Start a new workbook Start recording a macro that will be stored in this macro workbook. File|Open your text file that needs to be imported. Format it the way you like. Insert columns, add headers, freeze panes, widen columns, add filters, do the page setup--everything you can think of. When you're done, save that workbook with the macro. I like to put a big button from the Forms toolbar on the only worksheet in that macro workbook and assign this macro to that big button. I'll add a few instructions to that sheet, too. If the file name to open is always the same and in the same location, then I'm about done. If the location or file name changes, I'll tweak the code to ask for the file. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Ripper wrote: How do I import a file to Excel with a set of specifications? I don't want to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
Thanks Dave, very useful as usual!
Could you explain though why, as you say, "I'd stay away from using the filename and I'd stay away from using the windows collection, too"? I've done that a lot in various codes I've created and also from code I've gleaned here, without a problem. Could you also please explain the dot that proceeds lines of code within With & End With statements. What are the general rules with that? Rob "Dave Peterson" wrote in message ... I'd stay away from using the filename and I'd stay away from using the windows collection, too. You can make a couple more variables that represent the worksheet and workbook that holds the imported data. Option Explicit Sub Testme01() Dim myFileName As Variant dim wks as worksheet dim wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! 'while the .txt file is the activesheet set wks = activesheet set wkbk = activeworkbook 'or wks.parent 'then you can use: with wks .range("a1").value = "hi there" end with End Sub RobN wrote: Dave, How do I modify/add to this code so that once the text file has been opened I can go back and forth to it not knowing the name of the file until after it's been selected? ie I think I need to declare a different variable for the name of the file I just opened. The "myFileName" refers to the full path and I think I just want the name of the file. eg in my code I have Windows("SupporterStats.txt").Activate But I want to use the code, below, to open any text file and then run some more code, before acting again on the unknown name text file. Rob "Dave Peterson" wrote in message ... Saved from a previous post: Start a new workbook Start recording a macro that will be stored in this macro workbook. File|Open your text file that needs to be imported. Format it the way you like. Insert columns, add headers, freeze panes, widen columns, add filters, do the page setup--everything you can think of. When you're done, save that workbook with the macro. I like to put a big button from the Forms toolbar on the only worksheet in that macro workbook and assign this macro to that big button. I'll add a few instructions to that sheet, too. If the file name to open is always the same and in the same location, then I'm about done. If the location or file name changes, I'll tweak the code to ask for the file. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Ripper wrote: How do I import a file to Excel with a set of specifications? I don't want to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
The easier one first.
You have to match the windows name exactly. So if the user does Window|New window, you'll see something like: Textfile.txt:1 and textfile.txt:2. So referring to windows("textfile.txt") will fail. And (to me), it's more work to parse the name of the file than it is to just use a variable that represents the workbook or worksheet. In fact, I wouldn't use a workbook variable (well, sometimes). I'd just use: dim wks as worksheet workbooks.opentext .... set wks = activesheet Then if I need the workbook, I'd just use wks.parent. And I think that using nice variable names makes the code easier to debug later on: TextWks.range("a1").value = "hi" workbooks(filenamevariable).worksheets(1).range("a 1").value = "hi" RobN wrote: Thanks Dave, very useful as usual! Could you explain though why, as you say, "I'd stay away from using the filename and I'd stay away from using the windows collection, too"? I've done that a lot in various codes I've created and also from code I've gleaned here, without a problem. Could you also please explain the dot that proceeds lines of code within With & End With statements. What are the general rules with that? Rob "Dave Peterson" wrote in message ... I'd stay away from using the filename and I'd stay away from using the windows collection, too. You can make a couple more variables that represent the worksheet and workbook that holds the imported data. Option Explicit Sub Testme01() Dim myFileName As Variant dim wks as worksheet dim wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! 'while the .txt file is the activesheet set wks = activesheet set wkbk = activeworkbook 'or wks.parent 'then you can use: with wks .range("a1").value = "hi there" end with End Sub RobN wrote: Dave, How do I modify/add to this code so that once the text file has been opened I can go back and forth to it not knowing the name of the file until after it's been selected? ie I think I need to declare a different variable for the name of the file I just opened. The "myFileName" refers to the full path and I think I just want the name of the file. eg in my code I have Windows("SupporterStats.txt").Activate But I want to use the code, below, to open any text file and then run some more code, before acting again on the unknown name text file. Rob "Dave Peterson" wrote in message ... Saved from a previous post: Start a new workbook Start recording a macro that will be stored in this macro workbook. File|Open your text file that needs to be imported. Format it the way you like. Insert columns, add headers, freeze panes, widen columns, add filters, do the page setup--everything you can think of. When you're done, save that workbook with the macro. I like to put a big button from the Forms toolbar on the only worksheet in that macro workbook and assign this macro to that big button. I'll add a few instructions to that sheet, too. If the file name to open is always the same and in the same location, then I'm about done. If the location or file name changes, I'll tweak the code to ask for the file. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Ripper wrote: How do I import a file to Excel with a set of specifications? I don't want to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
Dave, Thanks, all makes good sense. I'll have to try the wks.parent
suggestion at some stage. (I guess you're working on the other Q when you can. I hope I'm not wasting your time. I can't seem to find what I'm looking for in Help.) Rob "Dave Peterson" wrote in message ... The easier one first. You have to match the windows name exactly. So if the user does Window|New window, you'll see something like: Textfile.txt:1 and textfile.txt:2. So referring to windows("textfile.txt") will fail. And (to me), it's more work to parse the name of the file than it is to just use a variable that represents the workbook or worksheet. In fact, I wouldn't use a workbook variable (well, sometimes). I'd just use: dim wks as worksheet workbooks.opentext .... set wks = activesheet Then if I need the workbook, I'd just use wks.parent. And I think that using nice variable names makes the code easier to debug later on: TextWks.range("a1").value = "hi" workbooks(filenamevariable).worksheets(1).range("a 1").value = "hi" RobN wrote: Thanks Dave, very useful as usual! Could you explain though why, as you say, "I'd stay away from using the filename and I'd stay away from using the windows collection, too"? I've done that a lot in various codes I've created and also from code I've gleaned here, without a problem. Could you also please explain the dot that proceeds lines of code within With & End With statements. What are the general rules with that? Rob "Dave Peterson" wrote in message ... I'd stay away from using the filename and I'd stay away from using the windows collection, too. You can make a couple more variables that represent the worksheet and workbook that holds the imported data. Option Explicit Sub Testme01() Dim myFileName As Variant dim wks as worksheet dim wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! 'while the .txt file is the activesheet set wks = activesheet set wkbk = activeworkbook 'or wks.parent 'then you can use: with wks .range("a1").value = "hi there" end with End Sub RobN wrote: Dave, How do I modify/add to this code so that once the text file has been opened I can go back and forth to it not knowing the name of the file until after it's been selected? ie I think I need to declare a different variable for the name of the file I just opened. The "myFileName" refers to the full path and I think I just want the name of the file. eg in my code I have Windows("SupporterStats.txt").Activate But I want to use the code, below, to open any text file and then run some more code, before acting again on the unknown name text file. Rob "Dave Peterson" wrote in message ... Saved from a previous post: Start a new workbook Start recording a macro that will be stored in this macro workbook. File|Open your text file that needs to be imported. Format it the way you like. Insert columns, add headers, freeze panes, widen columns, add filters, do the page setup--everything you can think of. When you're done, save that workbook with the macro. I like to put a big button from the Forms toolbar on the only worksheet in that macro workbook and assign this macro to that big button. I'll add a few instructions to that sheet, too. If the file name to open is always the same and in the same location, then I'm about done. If the location or file name changes, I'll tweak the code to ask for the file. My tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.OpenText Filename:=myFileName '....rest of recorded code here! End Sub Ripper wrote: How do I import a file to Excel with a set of specifications? I don't want to have to set the fixed length every time I want to import the file. -- Thanks As Always Rip -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
Oops. I didn't notice the last question.
I guess that there's a speed improvement when you use with/end with instead of qualifying each reference explicitly, but the two biggest values I see is that it makes the code easier to type and lots easier to read: dim myRng as range with worksheets("somesheetnamehere") set myrng = .range(.cells(somerow,somecol), _ .cells(someotherrow, someothercolumn)) end with is much easier to understand than this with all the extra clutter: set myrng = worksheets("somesheetnamehere") _ .range(worksheets("somesheetnamehere").cells(somer ow,somecol), _ worksheets("somesheetnamehere") _ .cells(someotherrow, someothercolumn)) And if I wanted to specify the workbook, things don't change much in this code. with workbooks("someworkbookhere.xls").worksheets("some sheetnamehere") set myrng = .range(.cells(somerow,somecol), _ .cells(someotherrow, someothercolumn)) end with I'll leave it to you to do the second version! ======= As for the leading dot, that means that the thing that follows the dot belongs to the object in the previous with statement--just like you expected. And you can nest them: with workbooks("somename.xls") with .worksheets("sheet1") with .range("a1") .numberformat = "General" .value = "hi there" end with with .range("b33") .numberformat = "General" .value = "bye there" end with end with 'and you don't need to get out of all of them until you want with workbooks("book2.xls").worksheets("sheet33") .range("a1") = "xxx" .range("b2") = "yyy" end with 'back to somename.xls with .worksheets("sheet2") .range("c99").clearcontents end with end with RobN wrote: <<snipped Could you also please explain the dot that proceeds lines of code within With & End With statements. What are the general rules with that? Rob |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Import File With Specifications
Dave, you've been a great help. Thankyou so much for your time in
explaining this so well! Rob "Dave Peterson" wrote in message ... Oops. I didn't notice the last question. I guess that there's a speed improvement when you use with/end with instead of qualifying each reference explicitly, but the two biggest values I see is that it makes the code easier to type and lots easier to read: dim myRng as range with worksheets("somesheetnamehere") set myrng = .range(.cells(somerow,somecol), _ .cells(someotherrow, someothercolumn)) end with is much easier to understand than this with all the extra clutter: set myrng = worksheets("somesheetnamehere") _ .range(worksheets("somesheetnamehere").cells(somer ow,somecol), _ worksheets("somesheetnamehere") _ .cells(someotherrow, someothercolumn)) And if I wanted to specify the workbook, things don't change much in this code. with workbooks("someworkbookhere.xls").worksheets("some sheetnamehere") set myrng = .range(.cells(somerow,somecol), _ .cells(someotherrow, someothercolumn)) end with I'll leave it to you to do the second version! ======= As for the leading dot, that means that the thing that follows the dot belongs to the object in the previous with statement--just like you expected. And you can nest them: with workbooks("somename.xls") with .worksheets("sheet1") with .range("a1") .numberformat = "General" .value = "hi there" end with with .range("b33") .numberformat = "General" .value = "bye there" end with end with 'and you don't need to get out of all of them until you want with workbooks("book2.xls").worksheets("sheet33") .range("a1") = "xxx" .range("b2") = "yyy" end with 'back to somename.xls with .worksheets("sheet2") .range("c99").clearcontents end with end with RobN wrote: <<snipped Could you also please explain the dot that proceeds lines of code within With & End With statements. What are the general rules with that? Rob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More than 3 specifications for conditional formatting in Excel | Excel Discussion (Misc queries) | |||
Would Like to Automate Batch File Creation and Text FIle Import | Excel Discussion (Misc queries) | |||
How can I automatically rearrange cells to my specifications? | Excel Discussion (Misc queries) | |||
How to increase colums specifications? | Setting up and Configuration of Excel | |||
Where can I find a template for application report specifications. | Excel Discussion (Misc queries) |