Vinylpusher,
Excel VBA is quite good to use. There are a number of ways to access worksheets and transfer data around.
1. Use a worksheet to temporarely store you import data (you can process each line manually (+ validate) or you should be able to use the built-in functions to open the file as a worksheet and go from there).
2. Accessing data on worksheets - there are a couple of different ways to access stuff, the best is generally to reference it, but if you need to cut/paste or format you will need to access directly.
Examples:
Reference:
The benefit of this method is that it is much faster and you don't need to change between worksheets, but formatting can be harder to do (best for pre-formatted templates)
Direct Access:
Is slower as you need to swap between sheets to do stuff (generally how code would be if you record macros). Generally easier to format on the fly.
Tips:
UserForms - pretty limited compared to standard Visual Basic 6 forms, so you can't do full-on stuff easily (say a wizard), but you can reference controls (see below)
Referencing controls - you can do this (particulary with Excel 2000 or above), but unless you can guarantee the control exists on the users machine, the workbook will fall-over when opening.
Recording macros - can be quite handy, but hand-coded stuff will perform better and probably be more accurate.
Option Explicit - good coding will always have this set - always much easier to debug.
Screen Updating - always turn it off and on as necessary - makes the program look more professional
Sub Auto_Open - use this sub when you startup as you can set the title of your application, maximise the screen and create menus or toolbars - again makes the product more professional.
Using ThisWorkbook in Project Tree -> Microsoft Excel Objects -> ThisWorkbook. You can do some good stuff here as well.
Excel VBA is quite good to use. There are a number of ways to access worksheets and transfer data around.
1. Use a worksheet to temporarely store you import data (you can process each line manually (+ validate) or you should be able to use the built-in functions to open the file as a worksheet and go from there).
2. Accessing data on worksheets - there are a couple of different ways to access stuff, the best is generally to reference it, but if you need to cut/paste or format you will need to access directly.
Examples:
Reference:
Dim worksheetA as Worksheet Dim worksheetB as Worksheet 'Turn off screen updating Application.ScreenUpdating = False Set worksheetA = ThisWorkbook.Worksheets("SheetName") Set worksheetB = ThisWorkbook.Worksheets("SheetName") worksheetA.Unprotect Password:="password" worksheetB.Unprotect Password:="password" worksheetB.Range("A1").Value = worksheetA.Range("A1").Value With worksheetB.Range("A1").Interior .ColorIndex = 6 .Pattern = xlSolid End With worksheetA.Protect Password:="password" worksheetB.Protect Password:="password" 'Turn on screen updating Application.ScreenUpdating = True Set worksheetA = Nothing Set worksheetB = Nothing
The benefit of this method is that it is much faster and you don't need to change between worksheets, but formatting can be harder to do (best for pre-formatted templates)
Direct Access:
'Turn off screen updating Application.ScreenUpdating = False Worksheets("SheetNameB").Activate Activesheet.Unprotect Password:="password" Range("A1").Select Selection.Copy Worksheets("SheetNameA").Activate Activesheet.Unprotect Password:="password" Range("A1").Select ActiveSheet.Paste With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Activesheet.Protect Password:="password" Worksheets("SheetNameB").Activate Activesheet.Protect Password:="password" 'Turn on screen updating Application.ScreenUpdating = True
Is slower as you need to swap between sheets to do stuff (generally how code would be if you record macros). Generally easier to format on the fly.
Tips:
UserForms - pretty limited compared to standard Visual Basic 6 forms, so you can't do full-on stuff easily (say a wizard), but you can reference controls (see below)
Referencing controls - you can do this (particulary with Excel 2000 or above), but unless you can guarantee the control exists on the users machine, the workbook will fall-over when opening.
Recording macros - can be quite handy, but hand-coded stuff will perform better and probably be more accurate.
Option Explicit - good coding will always have this set - always much easier to debug.
Screen Updating - always turn it off and on as necessary - makes the program look more professional
Sub Auto_Open - use this sub when you startup as you can set the title of your application, maximise the screen and create menus or toolbars - again makes the product more professional.
Sub Auto_Open() 'Set title Application.Caption = "Product Name" 'Maximise Excel for user Application.windowState = xlMaximized End Sub
Using ThisWorkbook in Project Tree -> Microsoft Excel Objects -> ThisWorkbook. You can do some good stuff here as well.
Private Sub Workbook_Activate() 'Set title Application.Caption = "ProgramName" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim msgboxResponse 'Prepare Cancel = False 'Prompt closure msgboxResponse = MsgBox("Are you sure you want to exit ProgramName?", vbYesNo + vbDefaultButton2 + vbQuestion, "Exit Program") If msgboxResponse = vbNo Then 'Stop closure of program Cancel = True End If End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Ensure user cannot save workbook 'Cancel = True End Sub Private Sub Workbook_Deactivate() 'User has changed from this workbook - reset excel caption Application.Caption = "" End Sub