Allow user input into VBA Module?

[Joel Mills]

I would like to be able to allow a user input the selected DBF file name
into the code instead of instructing them to name the DBF file as export.
Below is the code I currently use to copy the data into my worksheet. Also
there would need to be an error message that let them know that the file
wasn't opened if they either forget to open the file before running the
macro or misspell the name.

Any help would be appreciated.

Joel



Sub CopyData()

Worksheets("Export").Activate
'
Dim Asheet, Abook As String
Abook = ActiveWorkbook.Name
Asheet = ActiveSheet.Name

'This copies the data from the dbf file into the Worksheet Titled "Export"
Windows("Export.DBF").Activate
Range("A1").Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Windows(Abook).Activate
Worksheets(Asheet).Activate
Range("A1").Select
ActiveSheet.Paste
Worksheets("Instructions").Activate
End Sub

 

[Tom Ogilvy]

Dim bk as Workbook
Dim sName as String
Dim sName1 as String
sName = Inputbox("Please Enter the file name of the open data base" & _
vbNewLine & " ex: Export.dbf", Default:="Export.dbf")
if sname = "" then exit sub
On Error Resume Next
if lcase(right(sname,3)) <> "dbf" then
sName = sName & ".dbf"
end if
set bk = workbooks(sName)
On Error goto 0
if bk is nothing then
Msgbox sName & " isn't currently open, please open it"
sName1 = Application.GetOpenfilename()
if sName1 <> "False" then
set bk = workbooks.Open(sName1)
else
exit sub
end if
end if
bk.worksheets(1).UsedRange.Copy Destination:= _
Workbooks(Abook).Worksheets(Asheet) _
.Range("A1")

 

[Joel Mills]

Tom,

Once again thanks for your help. This does exactly what I needed. I hope
others appreciate your assistance and can get some use from of this
question.

Joel

 

[K Dales]

Since the code you gave implies that the info was already put into an Excel
sheet (named "Export.DBF") is it correct to assume that your user will
already have a sheet with the data on it, except it might be another name?
If that is true, and if the sheet is named something.DBF, you could perhaps
do as follows:

Function FindSheet() as String
Dim ThisSheet as Worksheet
FindSheet = ""
For Each ThisSheet in Worksheets
If ThisSheet.Name like "*.DBF" Then FindSheet = ThisSheet.Name
Next ThisSheet

End Function

Sub CopyData()
Dim Asheet, Abook As String
Dim DBFSheet as String

Worksheets("Export").Activate
Abook = ActiveWorkbook.Name
Asheet = ActiveSheet.Name
DBFSheet = FindSheet()
If DBFSheet = "" Then
MsgBox "dbf file data not found!", vbExclamation, "ERROR:"
Else
'This copies the data from the dbf file into the Worksheet Titled "Export"
Windows(DBFSheet).Activate
Range("A1").Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Windows(Abook).Activate
Worksheets(Asheet).Activate
Range("A1").Select
ActiveSheet.Paste
End If
Worksheets("Instructions").Activate
End Sub

The other option would be similar but use an Inputbox or a cell value for
the user to enter the name of the file.

 

[Joel Mills]

K Dales,
Thankyou for the response. You are right, the info was exported as a dbf
file and I was wanting to add an input for the user and not limit them to
naming or renaming the file as export.dbf. I was able to use Tom's reply to
get my code to work.