Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user input into VBA Module?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user input into VBA Module?
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") -- Regards, Tom Ogilvy "Joel Mills" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user input into VBA Module?
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 "Tom Ogilvy" wrote in message ... 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") -- Regards, Tom Ogilvy "Joel Mills" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user input into VBA Module?
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Allow user input into VBA Module?
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. "K Dales" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt user for input and utilize that input | Excel Worksheet Functions | |||
Have user input converted to uppercase in same cell as input? | New Users to Excel | |||
Calling an Excel/VBA User Created Module from WSH | Excel Programming | |||
Making Contents of a cell input for a Module | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |