inputbox to import file

  • Thread starter Thread starter lechu
  • Start date Start date
L

lechu

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
 
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.
 
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
 
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
 
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.)
 
Thanks a lot Dave; it works great.
Dave said:
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.)
Click to expand...
 
Back
Top