Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
inputbox to import file
Greetings,
Target: append data to a table from a daily file is saved in a folder with name and date, "fileYYMMDD.csv" Problem: "sometimes" the sub-routine works, others doesn't: 1004 Run time Error, the file could not be found. In my laptop works flawlesly, and in the office sometimes works but I coun't not isolate the variable to determine the bug. Would you please help me with this challenge? Thank you. This is the routine I am using: Sub Name() Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 mybook = InputBox("Enter File Name to Open:", "imYYMMDD.csv Format") If mybook = "" Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=mybook ChDir "C:\Whatever path\Folder" Range("B1:B10") = "=Today()*1" Range("B1:D10").Copy Workbooks(mybook).Close ThisWorkbook.Activate Cells(NextRow, 2).Select ActiveSheet.Paste End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
inputbox to import file
Instead of using inputbox, how about just letting the user point at the file:
dim myBookName as variant mybookname = Application.GetOpenFilename("CSV Files, *.csv") if mybookname = false then exit sub end if 'keep on doing the work. lechu wrote: Greetings, Target: append data to a table from a daily file is saved in a folder with name and date, "fileYYMMDD.csv" Problem: "sometimes" the sub-routine works, others doesn't: 1004 Run time Error, the file could not be found. In my laptop works flawlesly, and in the office sometimes works but I coun't not isolate the variable to determine the bug. Would you please help me with this challenge? Thank you. This is the routine I am using: Sub Name() Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 mybook = InputBox("Enter File Name to Open:", "imYYMMDD.csv Format") If mybook = "" Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=mybook ChDir "C:\Whatever path\Folder" Range("B1:B10") = "=Today()*1" Range("B1:D10").Copy Workbooks(mybook).Close ThisWorkbook.Activate Cells(NextRow, 2).Select ActiveSheet.Paste End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
inputbox to import file
Any reason you're not using Application.GetOpenFilename?
Dim f1 as String f1 = Application.GetOpenFilename("CSV Files, *.csv", , "Please Select File", , False) This would eliminate operator malfunctions in typing the name. Charles lechu wrote: Greetings, Target: append data to a table from a daily file is saved in a folder with name and date, "fileYYMMDD.csv" Problem: "sometimes" the sub-routine works, others doesn't: 1004 Run time Error, the file could not be found. In my laptop works flawlesly, and in the office sometimes works but I coun't not isolate the variable to determine the bug. Would you please help me with this challenge? Thank you. This is the routine I am using: Sub Name() Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 mybook = InputBox("Enter File Name to Open:", "imYYMMDD.csv Format") If mybook = "" Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=mybook ChDir "C:\Whatever path\Folder" Range("B1:B10") = "=Today()*1" Range("B1:D10").Copy Workbooks(mybook).Close ThisWorkbook.Activate Cells(NextRow, 2).Select ActiveSheet.Paste End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
inputbox to import file
Thank you for your response;
I think I am missing a step, because although your advise works, I am unable to activate that file to insert date, and then copy the range. How can I select or activate the opened file? Thank you, Lechu Dave Peterson wrote: Instead of using inputbox, how about just letting the user point at the file: dim myBookName as variant mybookname = Application.GetOpenFilename("CSV Files, *.csv") if mybookname = false then exit sub end if 'keep on doing the work. lechu wrote: Greetings, Target: append data to a table from a daily file is saved in a folder with name and date, "fileYYMMDD.csv" Problem: "sometimes" the sub-routine works, others doesn't: 1004 Run time Error, the file could not be found. In my laptop works flawlesly, and in the office sometimes works but I coun't not isolate the variable to determine the bug. Would you please help me with this challenge? Thank you. This is the routine I am using: Sub Name() Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 mybook = InputBox("Enter File Name to Open:", "imYYMMDD.csv Format") If mybook = "" Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=mybook ChDir "C:\Whatever path\Folder" Range("B1:B10") = "=Today()*1" Range("B1:D10").Copy Workbooks(mybook).Close ThisWorkbook.Activate Cells(NextRow, 2).Select ActiveSheet.Paste End Sub -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
inputbox to import file
You still have to open it yourself.
option explicit sub testme() dim myFileName as variant dim newwkbk as workbook Dim NextRow As Long myfilename = application.getopenfilename("CSV files, *.csv") if myfilename = false then exit sub end if set curwks = activesheet 'the one that's visible before you do the Open with curwks NextRow = .Range("B65536").End(xlUp).Row + 1 end with set newwkbk = workbooks.open(filename:=myfilename) with newwkbk.worksheets(1) .Range("B1:B10").formula = "=Today()*1" .Range("B1:D10").Copy _ destination:=curwks.cells(nextrow,2) .parent.close savechanges:=false 'close the CSV file?? end with end sub (Untested, uncompiled. Watch for typos.) lechu wrote: Thank you for your response; I think I am missing a step, because although your advise works, I am unable to activate that file to insert date, and then copy the range. How can I select or activate the opened file? Thank you, Lechu Dave Peterson wrote: Instead of using inputbox, how about just letting the user point at the file: dim myBookName as variant mybookname = Application.GetOpenFilename("CSV Files, *.csv") if mybookname = false then exit sub end if 'keep on doing the work. lechu wrote: Greetings, Target: append data to a table from a daily file is saved in a folder with name and date, "fileYYMMDD.csv" Problem: "sometimes" the sub-routine works, others doesn't: 1004 Run time Error, the file could not be found. In my laptop works flawlesly, and in the office sometimes works but I coun't not isolate the variable to determine the bug. Would you please help me with this challenge? Thank you. This is the routine I am using: Sub Name() Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 mybook = InputBox("Enter File Name to Open:", "imYYMMDD.csv Format") If mybook = "" Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=mybook ChDir "C:\Whatever path\Folder" Range("B1:B10") = "=Today()*1" Range("B1:D10").Copy Workbooks(mybook).Close ThisWorkbook.Activate Cells(NextRow, 2).Select ActiveSheet.Paste End Sub -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
inputbox to import file
Thanks a lot Dave; it works great.
Dave Peterson wrote: You still have to open it yourself. option explicit sub testme() dim myFileName as variant dim newwkbk as workbook Dim NextRow As Long myfilename = application.getopenfilename("CSV files, *.csv") if myfilename = false then exit sub end if set curwks = activesheet 'the one that's visible before you do the Open with curwks NextRow = .Range("B65536").End(xlUp).Row + 1 end with set newwkbk = workbooks.open(filename:=myfilename) with newwkbk.worksheets(1) .Range("B1:B10").formula = "=Today()*1" .Range("B1:D10").Copy _ destination:=curwks.cells(nextrow,2) .parent.close savechanges:=false 'close the CSV file?? end with end sub (Untested, uncompiled. Watch for typos.) lechu wrote: Thank you for your response; I think I am missing a step, because although your advise works, I am unable to activate that file to insert date, and then copy the range. How can I select or activate the opened file? Thank you, Lechu Dave Peterson wrote: Instead of using inputbox, how about just letting the user point at the file: dim myBookName as variant mybookname = Application.GetOpenFilename("CSV Files, *.csv") if mybookname = false then exit sub end if 'keep on doing the work. lechu wrote: Greetings, Target: append data to a table from a daily file is saved in a folder with name and date, "fileYYMMDD.csv" Problem: "sometimes" the sub-routine works, others doesn't: 1004 Run time Error, the file could not be found. In my laptop works flawlesly, and in the office sometimes works but I coun't not isolate the variable to determine the bug. Would you please help me with this challenge? Thank you. This is the routine I am using: Sub Name() Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 mybook = InputBox("Enter File Name to Open:", "imYYMMDD.csv Format") If mybook = "" Then Exit Sub Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=mybook ChDir "C:\Whatever path\Folder" Range("B1:B10") = "=Today()*1" Range("B1:D10").Copy Workbooks(mybook).Close ThisWorkbook.Activate Cells(NextRow, 2).Select ActiveSheet.Paste End Sub -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Import Data with the filename coming from inputbox ? | Excel Programming | |||
inputbox vs. userform/combobox vs. Windows file handlers ??? | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming |