inputbox to import file

[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("B110").Copy
Workbooks(mybook).Close
ThisWorkbook.Activate
Cells(NextRow, 2).Select
ActiveSheet.Paste
End Sub

 

[Dave Peterson]

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.

 

[Die_Another_Day]

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]

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]

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("B110").Copy _
destination:=curwks.cells(nextrow,2)
.parent.close savechanges:=false 'close the CSV file??
end with

end sub

(Untested, uncompiled. Watch for typos.)

 

[lechu]

Thanks a lot Dave; it works great.

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("B110").Copy _
destination:=curwks.cells(nextrow,2)
.parent.close savechanges:=false 'close the CSV file??
end with

end sub

(Untested, uncompiled. Watch for typos.)