Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) '....rest of recorded code here! End Sub Paul H wrote: ================================================== ======= I prefer to have each report stand alone. All the .CSV files for one report will be in one folder. Other reports will have their own folders. So for each of the reports, I need to have the macro let the user browse the folder for his preferred file. Is this possible? Each spreadsheet will only need one "import" button on the left end of the toolbar. Can each .XLS have it's own macro embedded it? Or must the user have one workbook project, that contains the macros? This will be harder for me to maintain than if they stand alone. ================================================== ======= "Dave Peterson" wrote in message ... You could make a toolbar that has the 15-20 different macros on it. But I think I'd use multiple buttons from the Forms toolbar placed directly on a worksheet. And if some of those reports are always run at the same time as others, then I'd have one button that ran those reports. Caption one of the buttons "Click me to run reports 1-6" And assign a "combined" macro to that button: Option Explicit Sub DoReports_1_6() call DoReport1 call DoReport2 call DoReport3 call DoReport4 call DoReport5 call DoReport6 End Sub Where those doReport# procedures are in that same workbook's project. ==== By using buttons from the Forms toolbar placed directly on the worksheet, you can have as many notes as you like near that button. Paul H wrote: ================================================== ======= Dave, One user will be using these Excel reports. Each having many or few rows and columns. I will be developing 15 or 20 different reports for this user. Some will be run multiple times per day as batches are processed. Others may only be run once a week or once a month. The .CSV files will be in the same folder. The user is used to pointing to the correct file for other processes. So, the process can not require my participation. I like putting the "import" button at the left edge of the toolbar. Please comment... Thanks, Paul ================================================== ======= "Dave Peterson" wrote in message ... You can distribute the two (*.xls and *.csv) files, but that sounds kind of weird to me. If you only have a single *.csv file, then you should do the importing, save it as a .xls file and distribute that. If you have a *.csv file that's updated lots of times, then distribute the single *.xls file and then redistribute (as often as it's updated) the *.csv files. If you put the procedure in its own module, you can remove the module. Debra Dalgleish shows how: http://contextures.com/xlfaqMac.html#NoMacros If you used a single module and have lots of procedures, then you'll just select the procedure that you want to remove and hit the delete key -- just like deleting a word/phrase/paragraph in Word or clearing contents in an excel range. Be careful, though. Make sure you have a backup (just in case). You can do that by copying the .xls file to a safe location. And yep, your users will have to let macros run when they open your workbook. Paul H wrote: ================================================== ======= Will the macro stay with the .XLS file? Can I distribute just the 2 files - the .CSV file to be read, and the empty .XLS file with the name I want it to have? And what do I un-hide so I can remove a macro I want to do over or get rid of? I have set security low beause I trust my anto-virus. Will my users have a security problem, running my macro? ================================================== ======= Macros live in workbooks. So your macro (probably???) lives in that .xls file in the same folder as the .csv. But I don't have a real guess at where you stored this macro--could it be in your personal.xl* workbook? Personally, I don't like running this kind of macro by a shortcut or by using auto_open. I have to remember the shortcut and make sure that anyone who runs this when I'm not there (vacation???) can remember it, too. And I wouldn't use auto_open. If I open the workbook for some other purpose later in the day, I don't want to have it run automatically. Instead, I just plop a button from the Forms toolbar onto a worksheet and assign the macro to that button. Add some instructions (for both me and other users) to a separate worksheet (or below the button) and I'm set. This will mean that I don't have to check to see if the macro has been run earlier in the day in my code. But the choice is yours (obviously). ================================================== ======= Paul H wrote: Dave, I tried the macro solution - it works! I can read the entire 5005 row x 42 column file in about 3 seconds. Now I have confusion about the process. Where is the macro stored? I created a folder with 2 files - the .CSV file to be read and the empty .XLS file with the name I want it to have. I invoke it by pressing ctl-m. I'll change that to Auto_Open so it will run when I open the .XLS file. When I move the folder with the 2 files to another computer, the macro doesn't work. So the macro didn't come with the .XLS file. I don't know what you mean about the "You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end". Also, I want to delete a macro so I can re-record it, but it talks about un-hiding something. What? Thanks for your help. Paul ================================================== ======= "Patrick Molloy" wrote in message ... the macro recorder will show you the very basic VBA code. you'll want to tidy up :) but its a great way to start "Paul H" wrote in message ... I tried it using Excel 2003 and it works there also. I'll try the entire process tomorrow (Monday). Wish me luck, and thanks. Paul ================================================== ======= "JLatham" wrote in message ... In Excel 2007 you will need to be able to see the [Developer] tab. If it is not visible now, click the Office Button, then the [Excel Options] button near the lower right of the window that opens. In the "Popular" group, enable the "Show Developer tab in the ribbon" option. To record a macro, choose the Developer tab and click the "Record Macro" - give it a name and procede with the steps you wish to record. The "Record Macro" option will have changed to "Stop Recording". Click it when you have finished recording the steps you need to repeat later. "Paul H" wrote: ================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
Hi,
I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) '....rest of recorded code here! End Sub Paul H wrote: ================================================== ======= I prefer to have each report stand alone. All the .CSV files for one report will be in one folder. Other reports will have their own folders. So for each of the reports, I need to have the macro let the user browse the folder for his preferred file. Is this possible? Each spreadsheet will only need one "import" button on the left end of the toolbar. Can each .XLS have it's own macro embedded it? Or must the user have one workbook project, that contains the macros? This will be harder for me to maintain than if they stand alone. ================================================== ======= "Dave Peterson" wrote in message ... You could make a toolbar that has the 15-20 different macros on it. But I think I'd use multiple buttons from the Forms toolbar placed directly on a worksheet. And if some of those reports are always run at the same time as others, then I'd have one button that ran those reports. Caption one of the buttons "Click me to run reports 1-6" And assign a "combined" macro to that button: Option Explicit Sub DoReports_1_6() call DoReport1 call DoReport2 call DoReport3 call DoReport4 call DoReport5 call DoReport6 End Sub Where those doReport# procedures are in that same workbook's project. ==== By using buttons from the Forms toolbar placed directly on the worksheet, you can have as many notes as you like near that button. Paul H wrote: ================================================== ======= Dave, One user will be using these Excel reports. Each having many or few rows and columns. I will be developing 15 or 20 different reports for this user. Some will be run multiple times per day as batches are processed. Others may only be run once a week or once a month. The .CSV files will be in the same folder. The user is used to pointing to the correct file for other processes. So, the process can not require my participation. I like putting the "import" button at the left edge of the toolbar. Please comment... Thanks, Paul ================================================== ======= "Dave Peterson" wrote in message ... You can distribute the two (*.xls and *.csv) files, but that sounds kind of weird to me. If you only have a single *.csv file, then you should do the importing, save it as a .xls file and distribute that. If you have a *.csv file that's updated lots of times, then distribute the single *.xls file and then redistribute (as often as it's updated) the *.csv files. If you put the procedure in its own module, you can remove the module. Debra |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
You can get the default directory by performing a Chdrive and a chdir like
the code below. Because you are using a comma delimited file you don't need the arrays of 1's (just need if fixed width). I also eliminated optional options that aren't reauired and may be confusing. Option Explicit Sub Loader1() Dim myFileName As Variant CHdrive = "H" Chdir = "H:\my documents\temp" myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .FieldNames = True .RowNumbers = False .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileCommaDelimiter = True .TextFileTrailingMinusNumbers = True End With Range("A1:AO1").Font.Bold = True End Sub "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) '....rest of recorded code here! End Sub Paul H wrote: ================================================== ======= I prefer to have each report stand alone. All the .CSV files for one report will be in one folder. Other reports will have their own folders. So for each of the reports, I need to have the macro let the user browse the folder for his preferred file. Is this possible? Each spreadsheet will only need one "import" button on the left end of the toolbar. Can each .XLS have it's own macro embedded it? Or must the user have one workbook project, that contains the macros? This will be harder for me to maintain than if they stand alone. ================================================== ======= "Dave Peterson" wrote in message ... You could make a toolbar that has the 15-20 different macros on it. But I think I'd use multiple buttons from the Forms toolbar placed directly on a worksheet. And if some of those reports are always run at the same time as others, then I'd have one button that ran those reports. Caption one of the buttons "Click me to run reports 1-6" And assign a "combined" macro to that button: Option Explicit Sub DoReports_1_6() call DoReport1 call DoReport2 call DoReport3 call DoReport4 call DoReport5 call DoReport6 End Sub Where those doReport# procedures are in that same workbook's project. ==== By using buttons from the Forms toolbar placed directly on the worksheet, you can have as many notes as you like near that button. Paul H wrote: ================================================== ======= Dave, One user will be using these Excel reports. Each having many or few rows and columns. I will be developing 15 or 20 different reports for this user. Some will be run multiple times per day as batches are processed. Others may only be run once a week or once a month. The .CSV files will be in the same folder. The user is used to pointing to the correct file for other processes. So, the process can not require my participation. I like putting the "import" button at the left edge of the toolbar. Please comment... Thanks, Paul ================================================== ======= "Dave Peterson" wrote in message ... You can distribute the two (*.xls and *.csv) files, but that sounds kind of weird to me. If you only have a single *.csv file, then you should do the importing, save it as a .xls file and distribute that. If you have a *.csv file that's updated lots of times, then distribute the single *.xls file and then redistribute (as often as it's updated) the *.csv files. If you put the procedure in its own module, you can remove the module. Debra |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
If that path on the network drive is not mapped (you're using the UNC Path),
then chdir won't work. But there's a windows API that will work in all cases: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub Loader1() Dim myFileName As Variant Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "\\share\folder1\folder2" On Error Resume Next ChDirNet myNewFolder If Err.Number < 0 Then 'what should happen MsgBox "Please change to your own folder" Err.Clear End If On Error GoTo 0 myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") ChDirNet myCurFolder If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=activesheet.Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ActiveSheet.Range("A1:AO1").Font.Bold = True End Sub I left all the defaults in the code. You never know when you'll want to change them. (I thought some of the fields would be text (but I may be misremembering).) ps. Untested, but it did compile. Paul H wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) '....rest of recorded code here! End Sub Paul H wrote: ================================================== ======= I prefer to have each report stand alone. All the .CSV files for one report will be in one folder. Other reports will have their own folders. So for each of the reports, I need to have the macro let the user browse the folder for his preferred file. Is this possible? Each spreadsheet will only need one "import" button on the left end of the toolbar. Can each .XLS have it's own macro embedded it? Or must the user have one workbook project, that contains the macros? This will be harder for me to maintain than if they stand alone. ================================================== ======= "Dave Peterson" wrote in message ... You could make a toolbar that has the 15-20 different macros on it. But I think I'd use multiple buttons from the Forms toolbar placed directly on a worksheet. And if some of those reports are always run at the same time as others, then I'd have one button that ran those reports. Caption one of the buttons "Click me to run reports 1-6" And assign a "combined" macro to that button: Option Explicit Sub DoReports_1_6() call DoReport1 call DoReport2 call DoReport3 call DoReport4 call DoReport5 call DoReport6 End Sub Where those doReport# procedures are in that same workbook's project. ==== By using buttons from the Forms toolbar placed directly on the worksheet, you can have as many notes as you like near that button. Paul H wrote: ================================================== ======= Dave, One user will be using these Excel reports. Each having many or few rows and columns. I will be developing 15 or 20 different reports for this user. Some will be run multiple times per day as batches are processed. Others may only be run once a week or once a month. The .CSV files will be in the same folder. The user is used to pointing to the correct file for other processes. So, the process can not require my participation. I like putting the "import" button at the left edge of the toolbar. Please comment... Thanks, Paul ================================================== ======= "Dave Peterson" wrote in message ... You can distribute the two (*.xls and *.csv) files, but that sounds kind of weird to me. If you only have a single *.csv file, then you should do the importing, save it as a .xls file and distribute that. If you have a *.csv file that's updated lots of times, then distribute the single *.xls file and then redistribute (as often as it's updated) the *.csv files. If you put the procedure in its own module, you can remove the module. Debra Dalgleish shows how: http://contextures.com/xlfaqMac.html#NoMacros If you used a single module and have lots of procedures, then you'll just select the procedure that you want to remove and hit the delete key -- just like deleting a word/phrase/paragraph in Word or clearing contents in an excel range. Be careful, though. Make sure you have a backup (just in case). You can do that by copying the .xls file to a safe location. And yep, your users will have to let macros run when they open your workbook. Paul H wrote: ================================================== ======= Will the macro stay with the .XLS file? Can I distribute just the 2 files - the .CSV file to be read, and the empty .XLS file with the name I want it to have? And what do I un-hide so I can remove a macro I want to do over or get rid of? I have set security low beause I trust my anto-virus. Will my users have a security problem, running my macro? ================================================== ======= Macros live in workbooks. So your macro (probably???) lives in that .xls file in the same folder as the .csv. But I don't have a real guess at where you stored this macro--could it be in your personal.xl* workbook? Personally, I don't like running this kind of macro by a shortcut or by using auto_open. I have to remember the shortcut and make sure that anyone who runs this when I'm not there (vacation???) can remember it, too. And I wouldn't use auto_open. If I open the workbook for some other purpose later in the day, I don't want to have it run automatically. Instead, I just plop a button from the Forms toolbar onto a worksheet and assign the macro to that button. Add some instructions (for both me and other users) to a separate worksheet (or below the button) and I'm set. This will mean that I don't have to check to see if the macro has been run earlier in the day in my code. But the choice is yours (obviously). ================================================== ======= Paul H wrote: Dave, I tried the macro solution - it works! I can read the entire 5005 row x 42 column file in about 3 seconds. Now I have confusion about the process. Where is the macro stored? I created a folder with 2 files - the .CSV file to be read and the empty .XLS file with the name I want it to have. I invoke it by pressing ctl-m. I'll change that to Auto_Open so it will run when I open the .XLS file. When I move the folder with the 2 files to another computer, the macro doesn't work. So the macro didn't come with the .XLS file. I don't know what you mean about the "You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end". Also, I want to delete a macro so I can re-record it, but it talks about un-hiding something. What? Thanks for your help. Paul ================================================== ======= "Patrick Molloy" wrote in message ... the macro recorder will show you the very basic VBA code. you'll want to tidy up :) but its a great way to start "Paul H" wrote in message ... I tried it using Excel 2003 and it works there also. I'll try the entire process tomorrow (Monday). Wish me luck, and thanks. Paul ================================================== ======= "JLatham" wrote in message ... In Excel 2007 you will need to be able to see the [Developer] tab. If it is not visible now, click the Office Button, then the [Excel Options] button near the lower right of the window that opens. In the "Popular" group, enable the "Show Developer tab in the ribbon" option. To record a macro, choose the Developer tab and click the "Record Macro" - give it a name and procede with the steps you wish to record. The "Record Macro" option will have changed to "Stop Recording". Click it when you have finished recording the steps you need to repeat later. "Paul H" wrote: ================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
================================================== ======= Dave, The network drives are all mapped, and for each application, never change. Only the quantity of the .CSV files in the folder increases, but their names contain date and time stamps, so being able to display them, newest at the top, is important. For testing they are mapped (usually L: or S:) to folders off the root of my C: or D:. In production, they are on other computers. Joel, I like how you simplified the code - most of which was created when I recorded the macro. The skeleton empty .XLS or .XLSX file contains the sizes and formats of the columns. Does that still mean the "arrays of 1's" is not needed? So I can probably clean up other files if I want to. But I usually don't care about extraneous code if it doesn't show the process down or cause a problem. Thank you Dave, Joel, and Shane. ================================================== ======= "Dave Peterson" wrote in message ... If that path on the network drive is not mapped (you're using the UNC Path), then chdir won't work. But there's a windows API that will work in all cases: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub Loader1() Dim myFileName As Variant Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "\\share\folder1\folder2" On Error Resume Next ChDirNet myNewFolder If Err.Number < 0 Then 'what should happen MsgBox "Please change to your own folder" Err.Clear End If On Error GoTo 0 myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") ChDirNet myCurFolder If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=activesheet.Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ActiveSheet.Range("A1:AO1").Font.Bold = True End Sub I left all the defaults in the code. You never know when you'll want to change them. (I thought some of the fields would be text (but I may be misremembering).) ps. Untested, but it did compile. Paul H wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) '....rest of recorded code here! End Sub Paul H wrote: ================================================== ======= I prefer to have each report stand alone. All the .CSV files for one report will be in one folder. Other reports will have their own folders. So for each of the reports, I need to have the macro let the user browse the folder for his preferred file. Is this possible? Each spreadsheet will only need one "import" button on the left end of the toolbar. Can each .XLS have it's own macro embedded it? Or must the user have one workbook project, that contains the macros? This will be harder for me to maintain than if they stand alone. ================================================== ======= "Dave Peterson" wrote in message ... You could make a toolbar that has the 15-20 different macros on it. But I think I'd use multiple buttons from the Forms toolbar placed directly on a worksheet. And if some of those reports are always run at the same time as others, then I'd have one button that ran those reports. Caption one of the buttons "Click me to run reports 1-6" And assign a "combined" macro to that button: Option Explicit Sub DoReports_1_6() call DoReport1 call DoReport2 call DoReport3 call DoReport4 call DoReport5 call DoReport6 End Sub Where those doReport# procedures are in that same workbook's project. ==== By using buttons from the Forms toolbar placed directly on the worksheet, you can have as many notes as you like near that button. Paul H wrote: ================================================== ======= Dave, One user will be using these Excel reports. Each having many or few rows and columns. I will be developing 15 or 20 different reports for this user. Some will be run multiple times per day as batches are processed. Others may only be run once a week or once a month. The .CSV files will be in the same folder. The user is used to pointing to the correct file for other processes. So, the process can not require my participation. I like putting the "import" button at the left edge of the toolbar. Please comment... Thanks, Paul ================================================== ======= "Dave Peterson" wrote in message ... You can distribute the two (*.xls and *.csv) files, but that sounds kind of weird to me. If you only have a single *.csv file, then you should do the importing, save it as a .xls file and distribute that. If you have a *.csv file that's updated lots of times, then distribute the single *.xls file and then redistribute (as often as it's updated) the *.csv files. If you put the procedure in its own module, you can remove the module. Debra Dalgleish shows how: http://contextures.com/xlfaqMac.html#NoMacros If you used a single module and have lots of procedures, then you'll just select the procedure that you want to remove and hit the delete key -- just like deleting a word/phrase/paragraph in Word or clearing contents in an excel range. Be careful, though. Make sure you have a backup (just in case). You can do that by copying the .xls file to a safe location. And yep, your users will have to let macros run when they open your workbook. Paul H wrote: ================================================== ======= Will the macro stay with the .XLS file? Can I distribute just the 2 files - the .CSV file to be read, and the empty .XLS file with the name I want it to have? And what do I un-hide so I can remove a macro I want to do over or get rid of? I have set security low beause I trust my anto-virus. Will my users have a security problem, running my macro? ================================================== ======= Macros live in workbooks. So your macro (probably???) lives in that .xls file in the same folder as the .csv. But I don't have a real guess at where you stored this macro--could it be in your personal.xl* workbook? Personally, I don't like running this kind of macro by a shortcut or by using auto_open. I have to remember the shortcut and make sure that anyone who runs this when I'm not there (vacation???) can remember it, too. And I wouldn't use auto_open. If I open the workbook for some other purpose later in the day, I don't want to have it run automatically. Instead, I just plop a button from the Forms toolbar onto a worksheet and assign the macro to that button. Add some instructions (for both me and other users) to a separate worksheet (or below the button) and I'm set. This will mean that I don't have to check to see if the macro has been run earlier in the day in my code. But the choice is yours (obviously). ================================================== ======= Paul H wrote: Dave, I tried the macro solution - it works! I can read the entire 5005 row x 42 column file in about 3 seconds. Now I have confusion about the process. Where is the macro stored? I created a folder with 2 files - the .CSV file to be read and the empty .XLS file with the name I want it to have. I invoke it by pressing ctl-m. I'll change that to Auto_Open so it will run when I open the .XLS file. When I move the folder with the 2 files to another computer, the macro doesn't work. So the macro didn't come with the .XLS file. I don't know what you mean about the "You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end". Also, I want to delete a macro so I can re-record it, but it talks about un-hiding something. What? Thanks for your help. Paul ================================================== ======= "Patrick Molloy" wrote in message ... the macro recorder will show you the very basic VBA code. you'll want to tidy up :) but its a great way to start "Paul H" wrote in message ... I tried it using Excel 2003 and it works there also. I'll try the entire process tomorrow (Monday). Wish me luck, and thanks. Paul ================================================== ======= "JLatham" wrote in message ... In Excel 2007 you will need to be able to see the [Developer] tab. If it is not visible now, click the Office Button, then the [Excel Options] button near the lower right of the window that opens. In the "Popular" group, enable the "Show Developer tab in the ribbon" option. To record a macro, choose the Developer tab and click the "Record Macro" - give it a name and procede with the steps you wish to record. The "Record Macro" option will have changed to "Stop Recording". Click it when you have finished recording the steps you need to repeat later. "Paul H" wrote: ================================================== ======= How do I record a macro? Do you mean I can create a macro that will do my steps 1 thru 8, below? ================================================== ======= "Dave Peterson" wrote in message ... It's too difficult to push a button? If that's true, then name your macro Auto_Open. It'll run the first time someone opens the workbook. You could plop the date into a cell in a hidden sheet so that it only runs once. And add a save at the end. If that doesn't work, good luck with the automation. Paul H wrote: ================================================== ======= I need the COBOL program to properly format the data. Multiple end-users will use this, sometimes daily, with data that changes daily, so I have been requested to make it completely automatic. Another subsequent program shows them the choices of reports and automatically starts the one they select. ================================================== ======= "Dave Peterson" wrote in message ... Maybe you could drop the requirement that the COBOL program do it. Set up a workbook with two sheets (instructions for the user and the actual data). Record a macro that does all the work in the second sheet. The plop a button from the Forms toolbar onto the instruction sheet that calls that macro. You only have to rename the worksheet if you're doing File|Open (or the equivalent in code). If you use the import external data stuff, you can leave it named .csv. ================================================== ======= Paul H wrote: Thanks Dave, I used your "import text" method and it does succeed in importing my .CSV file into my empty .XLS file, in about 2 seconds. That is, after I manually: 1. Open empty XLS file. 2. Data, import external data, import data. 3. "Select Data Source" screen comes up. 4. Type in my xxxx.TXT file name. 5. Import wizard step 1 - select delimited, then next. 6. Import wizard step 2 - select only Comma, then finish. 7. Import data to existing worksheet. 8. It imports the entire file in about 2 seconds, formatted correctly, with columns as described in my empty .XLS file. Now I need to figure out how to accomplish these steps automatically by my COBOL program. Thanks again, Paul ================================================== ======= "Dave Peterson" wrote in message ... If you rename the .csv file to .txt, you may be able to import the file quicker by setting each field the way you want. Then you could format the numeric fields as percentages or whatever you needed. If you have fields that have implicit decimals, you could import them as Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty cell. Edit|copy, edit|paste special|Values and divide (all in code) to convert that field. ================================================== ======= Paul H wrote: I have an empty, formatted spreadsheet, that I created by writing a few rows into it from my COBOL program, field by field, then deleting all of the rows. The process of creating the entire XLS or XLSX runs much too slow (100 records per minute or less), and uses memory up, so can never allow me to finish converting some of my large CSV files into formatted XLS (Excel 2003) or XLSX (Excel 2007) files. Someone told me to "turn off continual re-calculating". How? I wonder if I can somehow import my large .CSV file into my .XLS empty formatted file? I cannot find a way. Any help would be appreciated. TIA, Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
I often use the macro recorder to get the prper syntax of methods, but I
alwasy modify the recorded maqcro to simplify the code. I also remove the Section methods which aren't efficient. Switching from worksheets slows down the code and make it hard to understand. I you need to format you r columns use "Numberformat" which is easier to understand. The number 1 in the array method is General Formating which only insures that any formating on the worksheet is removed. "Paul H" wrote: ================================================== ======= Dave, The network drives are all mapped, and for each application, never change. Only the quantity of the .CSV files in the folder increases, but their names contain date and time stamps, so being able to display them, newest at the top, is important. For testing they are mapped (usually L: or S:) to folders off the root of my C: or D:. In production, they are on other computers. Joel, I like how you simplified the code - most of which was created when I recorded the macro. The skeleton empty .XLS or .XLSX file contains the sizes and formats of the columns. Does that still mean the "arrays of 1's" is not needed? So I can probably clean up other files if I want to. But I usually don't care about extraneous code if it doesn't show the process down or cause a problem. Thank you Dave, Joel, and Shane. ================================================== ======= "Dave Peterson" wrote in message ... If that path on the network drive is not mapped (you're using the UNC Path), then chdir won't work. But there's a windows API that will work in all cases: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub Loader1() Dim myFileName As Variant Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "\\share\folder1\folder2" On Error Resume Next ChDirNet myNewFolder If Err.Number < 0 Then 'what should happen MsgBox "Please change to your own folder" Err.Clear End If On Error GoTo 0 myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") ChDirNet myCurFolder If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=activesheet.Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ActiveSheet.Range("A1:AO1").Font.Bold = True End Sub I left all the defaults in the code. You never know when you'll want to change them. (I thought some of the fields would be text (but I may be misremembering).) ps. Untested, but it did compile. Paul H wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
================================================== ======= Joel, Getting error or ChDrive = "C" - says "Compile error: Argument not optional" in a box. Also can't figure out how to put my macro into a new workbook so I can distribute this spreadsheet by itself, with a button to cause our macro to run. I can't get the button to point to the new macro that stands alone in a new workbook. I'll want to put the output somewhere maybe a different folder, but I doubt it) having the same name, but extension .XLS. My new book helps a lot, but leaves a few details like this or me to figure out. Thanks, Paul ================================================== ======= "Joel" wrote in message ... I often use the macro recorder to get the prper syntax of methods, but I alwasy modify the recorded maqcro to simplify the code. I also remove the Section methods which aren't efficient. Switching from worksheets slows down the code and make it hard to understand. I you need to format you r columns use "Numberformat" which is easier to understand. The number 1 in the array method is General Formating which only insures that any formating on the worksheet is removed. "Paul H" wrote: ================================================== ======= Dave, The network drives are all mapped, and for each application, never change. Only the quantity of the .CSV files in the folder increases, but their names contain date and time stamps, so being able to display them, newest at the top, is important. For testing they are mapped (usually L: or S:) to folders off the root of my C: or D:. In production, they are on other computers. Joel, I like how you simplified the code - most of which was created when I recorded the macro. The skeleton empty .XLS or .XLSX file contains the sizes and formats of the columns. Does that still mean the "arrays of 1's" is not needed? So I can probably clean up other files if I want to. But I usually don't care about extraneous code if it doesn't show the process down or cause a problem. Thank you Dave, Joel, and Shane. ================================================== ======= "Dave Peterson" wrote in message ... If that path on the network drive is not mapped (you're using the UNC Path), then chdir won't work. But there's a windows API that will work in all cases: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub Loader1() Dim myFileName As Variant Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "\\share\folder1\folder2" On Error Resume Next ChDirNet myNewFolder If Err.Number < 0 Then 'what should happen MsgBox "Please change to your own folder" Err.Clear End If On Error GoTo 0 myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") ChDirNet myCurFolder If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=activesheet.Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ActiveSheet.Range("A1:AO1").Font.Bold = True End Sub I left all the defaults in the code. You never know when you'll want to change them. (I thought some of the fields would be text (but I may be misremembering).) ps. Untested, but it did compile. |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
The Chdrive command that works on 2003 XP is
ChDrive "C" You shouldn't be running a macro in another workbook. The better way is to put a macro in a newworkbook and in this macro have a GETOPENFILENAME command that opens the workbook with the data. Like this Private Sub CommandButton1_Click() fileToOpen = Application _ .GetOpenFilename("Excel Files (*.xls), *.xls*") If fileToOpen = False Then MsgBox("Cannot open file - Exiting Macro") exit Sub End If set Databk = workbooks.open(FileName:=filetoOpen) with DataBk 'put your code here end with Databk.close Savechanges:=True end sub "Paul H" wrote: ================================================== ======= Joel, Getting error or ChDrive = "C" - says "Compile error: Argument not optional" in a box. Also can't figure out how to put my macro into a new workbook so I can distribute this spreadsheet by itself, with a button to cause our macro to run. I can't get the button to point to the new macro that stands alone in a new workbook. I'll want to put the output somewhere maybe a different folder, but I doubt it) having the same name, but extension .XLS. My new book helps a lot, but leaves a few details like this or me to figure out. Thanks, Paul ================================================== ======= "Joel" wrote in message ... I often use the macro recorder to get the prper syntax of methods, but I alwasy modify the recorded maqcro to simplify the code. I also remove the Section methods which aren't efficient. Switching from worksheets slows down the code and make it hard to understand. I you need to format you r columns use "Numberformat" which is easier to understand. The number 1 in the array method is General Formating which only insures that any formating on the worksheet is removed. "Paul H" wrote: ================================================== ======= Dave, The network drives are all mapped, and for each application, never change. Only the quantity of the .CSV files in the folder increases, but their names contain date and time stamps, so being able to display them, newest at the top, is important. For testing they are mapped (usually L: or S:) to folders off the root of my C: or D:. In production, they are on other computers. Joel, I like how you simplified the code - most of which was created when I recorded the macro. The skeleton empty .XLS or .XLSX file contains the sizes and formats of the columns. Does that still mean the "arrays of 1's" is not needed? So I can probably clean up other files if I want to. But I usually don't care about extraneous code if it doesn't show the process down or cause a problem. Thank you Dave, Joel, and Shane. ================================================== ======= "Dave Peterson" wrote in message ... If that path on the network drive is not mapped (you're using the UNC Path), then chdir won't work. But there's a windows API that will work in all cases: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub Loader1() Dim myFileName As Variant Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "\\share\folder1\folder2" On Error Resume Next ChDirNet myNewFolder If Err.Number < 0 Then 'what should happen MsgBox "Please change to your own folder" Err.Clear End If On Error GoTo 0 myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") ChDirNet myCurFolder If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=activesheet.Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With ActiveSheet.Range("A1:AO1").Font.Bold = True End Sub I left all the defaults in the code. You never know when you'll want to change them. (I thought some of the fields would be text (but I may be misremembering).) ps. Untested, but it did compile |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) '....rest of recorded code here! End Sub Paul H wrote: ================================================== ======= I prefer to have each report stand alone. All the .CSV files for one report will be in one folder. Other reports will have their own folders. So for each of the reports, I need to have the macro let the user browse the folder for his preferred file. Is this possible? Each spreadsheet will only need one "import" button on the left end of the toolbar. Can each .XLS have it's own macro embedded it? Or must the user have one workbook project, that contains the macros? This will be harder for me to maintain than if they stand alone. ================================================== ======= "Dave Peterson" wrote in message ... You could make a toolbar that has the 15-20 different macros on it. But I think I'd use multiple buttons from the Forms toolbar placed directly on a worksheet. And if some of those reports are always run at the same time as others, then I'd have one button that ran those reports. Caption one of the buttons "Click me to run reports 1-6" And assign a "combined" macro to that button: Option Explicit Sub DoReports_1_6() call DoReport1 call DoReport2 call DoReport3 call DoReport4 call DoReport5 call DoReport6 End Sub Where those doReport# procedures are in that same workbook's project. ==== By using buttons from the Forms toolbar placed directly on the worksheet, you can have as many notes as you like near that button. Paul H wrote: ================================================== ======= Dave, One user will be using these Excel reports. Each having many or few rows and columns. I will be developing 15 or 20 different reports for this user. Some will be run multiple times per day as batches are processed. Others may only be run once a week or once a month. The .CSV files will be in the same folder. The user is used to pointing to the correct file for other processes. So, the process can not require my participation. I like putting the "import" button at the left edge of the toolbar. Please comment... Thanks, Paul ================================================== ======= "Dave Peterson" wrote in message ... You can distribute the two (*.xls and *.csv) files, but that sounds kind of weird to me. If you only have a single *.csv file, then you should do the importing, save it as a .xls file and distribute that. If you have a *.csv file that's updated lots of times, then distribute the single *.xls file and then redistribute (as often as it's updated) the *.csv files. If you put the procedure in its own module, you can remove the module. Debra |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS
I'm not sure I understand your problem. Do you wan to only display the files
in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If |
#11
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS part 5
================================================== ======== Joel or Shane, click on this link to a long web page that I created to show screen-print examples of my progress. www.verifycharges.com/Excel1/sample1.htm ================================================== ======== "Joel" wrote in message ... I'm not sure I understand your problem. Do you wan to only display the files in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help ================================================== ======== "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul ================================================== ======= Untested: Option Explicit Sub Loader1() Dim myFileName As Variant myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & myFileName, Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Range("A1:AO1").Font.Bold = True End Sub Paul H wrote: ================================================== ======= Dave, I have no idea what I'm doing - is there book on this subject. I clicked "step into" to get into the VB editor process, then pasted your stuff in. Should your stuff go 1st? Paul Sub Loader1() ' ' Loader1 Macro ' Macro recorded 6/30/2009 by Paul Hoberg ' ' Keyboard Shortcut: Ctrl+m ' With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1")) .Name = "CreditData-021809dater" .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 = False .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) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk As Workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If set wkbk = Workbooks.Open(Filename:=Filename:=myFileName) Range("A1").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 ActiveWindow.ScrollColumn = 35 ActiveWindow.ScrollColumn = 36 Range("A1:AO1").Select Selection.Font.Bold = True End Sub ================================================== ======= "Dave Peterson" wrote in message ... You can use as many workbooks as you want. You can have 20 different workbooks or a single workbook. Or even a few--where you put the macros that generate similar reports in those few workbooks. You could use something like this to ask for the filename: tweaked code could look a little like: Option Explicit Sub Testme01() Dim myFileName As Variant Dim Wkbk as workbook myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If |
#12
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS part 5
no "="
use ChDrive "C" "Paul H" wrote in message ... ================================================== ======== Joel or Shane, click on this link to a long web page that I created to show screen-print examples of my progress. www.verifycharges.com/Excel1/sample1.htm ================================================== ======== "Joel" wrote in message ... I'm not sure I understand your problem. Do you wan to only display the files in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help ================================================== ======== "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul |
#13
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS part 5
================================================== ======== Please show me what you suggest I try, and I will try it. ================================================== ======== "Patrick Molloy" wrote in message ... no "=" use ChDrive "C" ================================================== ======== "Paul H" wrote in message ... ================================================== ======== Joel or Shane, click on this link to a long web page that I created to show screen-print examples of my progress. www.verifycharges.com/Excel1/sample1.htm ================================================== ======== "Joel" wrote in message ... I'm not sure I understand your problem. Do you wan to only display the files in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help ================================================== ======== "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul |
#14
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS part 5
?
your link suggests that you want to set the file path to the folder .... your code is incorrect ... you have ChDrive = "C" it should be ChDrive "C" so ChDrive "C" ChDir "C:\Temp" myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") "Paul H" wrote in message ... ================================================== ======== Please show me what you suggest I try, and I will try it. ================================================== ======== "Patrick Molloy" wrote in message ... no "=" use ChDrive "C" ================================================== ======== "Paul H" wrote in message ... ================================================== ======== Joel or Shane, click on this link to a long web page that I created to show screen-print examples of my progress. www.verifycharges.com/Excel1/sample1.htm ================================================== ======== "Joel" wrote in message ... I'm not sure I understand your problem. Do you wan to only display the files in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help ================================================== ======== "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul |
#15
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS part 5
Thanks Patrick,
It worked! It showed the correct folder. But how did it know to use "detail" view? Will that be consistent? I've modified the web page www.verifycharges.com/Excel1/sample1.htm and added files to the c:\aaa folder. Now if I can make the button point to the correct macro and execute it, I'm done. Paul "Patrick Molloy" wrote in message ... ? your link suggests that you want to set the file path to the folder .... your code is incorrect ... you have ChDrive = "C" it should be ChDrive "C" so ChDrive "C" ChDir "C:\Temp" myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") "Paul H" wrote in message ... ================================================== ======== Please show me what you suggest I try, and I will try it. ================================================== ======== "Patrick Molloy" wrote in message ... no "=" use ChDrive "C" ================================================== ======== "Paul H" wrote in message ... ================================================== ======== Joel or Shane, click on this link to a long web page that I created to show screen-print examples of my progress. www.verifycharges.com/Excel1/sample1.htm ================================================== ======== "Joel" wrote in message ... I'm not sure I understand your problem. Do you wan to only display the files in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help ================================================== ======== "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul |
#16
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Still more on loading a .CSV into a .XSLS part 5
I don't get Detail view - so I'd guess that the user will see whatever File
Exploder has as default or maybe was last set to? So consistent? No. "Paul H" wrote in message ... Thanks Patrick, It worked! It showed the correct folder. But how did it know to use "detail" view? Will that be consistent? I've modified the web page www.verifycharges.com/Excel1/sample1.htm and added files to the c:\aaa folder. Now if I can make the button point to the correct macro and execute it, I'm done. Paul "Patrick Molloy" wrote in message ... ? your link suggests that you want to set the file path to the folder .... your code is incorrect ... you have ChDrive = "C" it should be ChDrive "C" so ChDrive "C" ChDir "C:\Temp" myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") "Paul H" wrote in message ... ================================================== ======== Please show me what you suggest I try, and I will try it. ================================================== ======== "Patrick Molloy" wrote in message ... no "=" use ChDrive "C" ================================================== ======== "Paul H" wrote in message ... ================================================== ======== Joel or Shane, click on this link to a long web page that I created to show screen-print examples of my progress. www.verifycharges.com/Excel1/sample1.htm ================================================== ======== "Joel" wrote in message ... I'm not sure I understand your problem. Do you wan to only display the files in a certain folder? You can use a listbox and manually fill the list box with the items in one folder. I believe if you use a win32 object you can lock the dialog to display only one folder. If you want to set a default folder then use the filedialog method instead of GetOpenFilename. See VBA help ================================================== ======== "Paul H" wrote: Shane, will you show me what to specify if my files are in C:\aaa but I cannot change from the folder where I am running my programs? Your help explanation is not clear. My actual path will be much more complex, involving a mapped drive, but the C:\aaa example should set me right. "Shane Devenshire" wrote in message ... Hi, I don't have time to try to follow all the earlier stuff in this thread, however the line myFileName = Application.GetOpenFilename("CSV Files, *.CSV",, "Pick a File") gets both the file name and path, so you just put myFileName in where ever you need it. As you can see I made a change to this line, not because it affect anything it just makes it easier to display on one line here. It also shows that the argument name is not technically necessary, in which case you must put the arguments in the correct order. Here is the Help explanation: Displays the standard Open dialog box and gets a file name from the user without actually opening any files. expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect) expression Required. An expression that returns an Application object. FileFilter Optional Variant. A string specifying file filtering criteria. This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters€” text and addin: "Text Files (*.txt),*.txt,Add-In Files (*.xla),*.xla". To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt". If omitted, this argument defaults to "All Files (*.*),*.*". FilterIndex Optional Variant. Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the title is "Open." ButtonText Optional Variant. Macintosh only. MultiSelect Optional Variant. True to allow multiple file names to be selected. False to allow only one file name to be selected. The default value is False Remarks This method returns the selected file name or the name entered by the user. The returned name may include a path specification. If MultiSelect is True, the return value is an array of the selected file names (even if only one filename is selected). Returns False if the user cancels the dialog box. This method may change the current drive or folder. Example This example displays the Open dialog box, with the file filter set to text files. If the user chooses a file name, the code displays that file name in a message box. fileToOpen = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If fileToOpen < False Then MsgBox "Open " & fileToOpen End If -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Paul H" wrote: ================================================== ======= Dave, Thank so much. It works perfectly. I just need to tell it what folder to be looking in, to make the file selection. It will be a long path on a networked drive. I bought a book that should help me figure these things out myself - Excel 2007 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) by John Walkenbach. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loading add ins | Excel Discussion (Misc queries) | |||
file extention .xsls | Setting up and Configuration of Excel | |||
Loading... | Excel Discussion (Misc queries) | |||
loading | Excel Discussion (Misc queries) | |||
Add-ins not loading | Excel Discussion (Misc queries) |