Importing Sheets

[Dowsey1977]

Hi,

I have a spreadsheet that is static on my machine. Users will send me forms on a regular basis (in Excel format) and I have to copy the sheet from the users into my spreadsheet and run a macro that moves data about. Because of the volume of forms that I will receive I wanted to add to my macro, or create a new one, that will automatically copy the sheet over.

Is this possible??

1 potential issue is that the sheet name may change, in which case, is there anyway that a 'Browse' button can be added in Excel to find the file, and then once found the sheet copied across?

Hope this is clear...if not, let me know.

:help :beerchug:

[geekgirlau]

I'd suggest that you create a template for the form filled in by the users (if you haven't already), and in the VBE window give the sheet a name. If you have both the Project Explorer and the Properties Window displayed (available in the View menu), you can click on the sheet in question and change the "Name" property to whatever you like (for example, "shForm").

This way it doesn't matter if the sheet is renamed (although if you protect the sheet this would not be an issue anyway) as you can refer to the sheet object name instead (for example, "shForm.Select"). Actually this is good practice in most code situations regardless of what you are doing!

[Dowsey1977]

Ok...so I have renamed the sheet in the properties (to shForm), so how do I get VBA to copy across the sheet? I found the below code in the KB on this site, but that was for .txt files etc... and not Excel. So can this just be modified to say 'import sheet named shForm??


Sub Import2ActiveCell()
Dim Filt$, TITLE$, FileText$, FileName$, N&
'//check that only one cell's been selected
If Selection.Cells.Count > 1 Then
MsgBox "Please select one cell only", , "Starting-Point is Unclear..."
Exit Sub
End If
'//show dialog to import file
'{Note: Office 2000 requires that
'(*.bas; *.frm; *.cls;*.txt;*.log;*.frx)
'be written twice, for later versions you
'can delete the second instance}
Filt = "VB Files (*.xls;*.txt) " & _
"(*.xls;*.txt)," & _
"*.xls;*.txt"
TITLE = "SELECT A FOLDER - DOUBLE-CLICK OR CLICK " & _
"OPEN TO IMPORT - CANCEL TO QUIT"
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, FilterIndex:=5, TITLE:=TITLE)
'//check there is a file to import
If Dir(FileName) <> Empty Then
'//import the text
Application.ScreenUpdating = False
Open (FileName) For Input As #1
N = ActiveCell.Row
Do While Not EOF(1)
Input #1, FileText
Cells(N, ActiveCell.Column) = FileText
N = N + 1
Loop '< Loop until end of file
Close #1
'//tart up the spreadsheet
ActiveWindow.DisplayGridlines = False
With Cells
.Font.Size = 9
Columns.AutoFit
Rows.AutoFit
End With
'//goto the start of the imported text & exit sub
ActiveCell.Select
End If
End Sub

[Bob Phillips]

Assuming the template data is on sheet 1


With ActiveWorkbook
Workbooks("Book1").Worksheets(1).Copy after:=.Worksheets(.Worksheets.Count)
End With