Importing Spreadsheet Files

If you have old Lotus 1-2-3 spreadsheet files, you can import data from them into Access tables using the TransferSpreadsheet method, which works much like the TransferText method, importing all the data from a worksheet. Unlike database files, you can use named argument values from the AcSpreadSheetType enum for spreadsheets of various versions; these values are listed in Table 10.2.

TABLE 10.2

SpreadsheetType Named Constants for Lotus 1-2-3

Lotus Version

SpreadsheetType Named Constants

Lotus WK1

acSpreadsheetTypeLotusWK1

Lotus WK3

acSpreadsheetTypeLotusWK3

Lotus WK4

acSpreadsheetTypeLotusWK4

Lotus WJ2

acSpreadsheetTypeLotusWJ2

When you inspect new job data imported from a Lotus spreadsheet (as shown in Figure 10.22), the Job Date/Time field looks strange, because the field is created as a Number field. However, when you click the Save Jobs to Table button, the CDate() function in the qappNewJobs append query converts the numeric date value into the correct format before appending the data to tblJobs. This type of tweaking of imported data is often required, to ensure that the data arriving in the target Access table is the correct data type.

FIGURE 10.22

Numeric values in the Job Date/Time field for data imported from a Lotus 1-2-3 spreadsheet.

S frmlmportAppData

Import Job Data from Application File

Product Number •

Job Date/Time

• Factory Name

Production Line

Batch Quantity - ±

FRB-1011024-B

38900.5423611111

Nutley

114

1122.24

FRB-1011024-B

38900.5423611111

Nutley

114

1122.24

FRB-1013889-B

38900.6965277778

Amberson

120

2233.22

FRB-1013889-B

38900.6965277778

Amberson

120

2233.22

FRB-1013898-C

38900.8645833333

Amberson

120

2334.44

FRB-1013898-C

38900.8645833333

Amberson

120

2334.44

FRB-1009697-C

38901.6826388889

Oakdale

143

425.29

FRB-1009697-C

38901,6326338339

Oakdale

143

425.29

FRB-1013775-B

38904.5993055556

Nutley

201

334.44

FRB-1013775- B

38904.5993055556

Nutley

201

334.44

FRB-1012727- B

38905.425

Oakdale

120

9098.22

FRB-1012727-B

38905.425

Oakdale

120

9098.22

FRB-1013085-D

38906.6826388889

Simms

143

223.22

FRB-1013085-D

38906.6326338889

Simms

143

223.22 w

Re

cord: H ¡1 of 200 |

H ^ 1

|Search

1

Source File ] D:\Documents\JobFilestoImport\Jobs.wk3

Import File Type Q dBASE © Paradox i Inspect New Jobs from App FiIe! Clear Imported Jobs Data

Source File ] D:\Documents\JobFilestoImport\Jobs.wk3

Import File Type Q dBASE © Paradox i Inspect New Jobs from App FiIe! Clear Imported Jobs Data

Save New Jobs to Table

The relevant procedures from the Import Job Data from Application File form module are listed next. The "Clear Imported Jobs Data" buttons event procedure clears the datasheet of records, calls the SaveAppFile Sub, clears the textbox of the file name, and deletes the temporary tables of new jobs:

Private Sub cmdClearData_Click() On Error Resume Next

Me![subNewJobs].SourceObject = Call SaveAppFile("") Me![txtSelectedAppFile].Value =

Delete new jobs tables

DoCmd.DeleteObject acTable, "tblNewJobs" DoCmd.DeleteObject acTable, "tblNewJobsDB"

End Sub

The "Inspect New Jobs from App File" buttons event procedure first checks that an application file has been selected, parses out the file path and file name for use in different arguments of the TransferDatabase statement, then sets up a Select Case statement to do the import differently for the three application types:

Private Sub cmdInspectJobs_Click() On Error GoTo ErrorHandler

Dim strText As String

Dim strTitle As String

Dim strPrompt As String

Dim txtData As Access.TextBox

Dim strAppFile As String

Dim strTable As String

Dim strSpec As String

Dim strDBPath As String

Dim strDBName As String

Dim strAppFileAndPath As String

Set txtData = Me![txtSelectedAppFile]

strAppFileAndPath = Nz(txtData.Value) If strAppFileAndPath = "" Then strTitle = "No application file selected" strPrompt = "Please select an application file" MsgBox prompt:=strPrompt, Buttons:=vbExclamation

+ vbOKOnly, title:=strTitle GoTo ErrorHandlerExit Else

Parse out file path and file name.

strDBPath = SplitDBPath(strAppFileAndPath)

Trim off last backslash.

Debug.Print "DB path length: " & Len(strDBPath) strDBPath = Left(strDBPath, Len(strDBPath) - 1) strDBName = SplitDBName(strAppFileAndPath) End If intFileType = Nz(Me![fraFileType].Value, 1) Select Case intFileType Case 1

dBASE

strTable = "tblNewJobsDB"

Debug.Print "DB Path: " & strDBPath

Debug.Print "DB Name: " & strDBName

DoCmd.TransferDatabase transfertype:=acImport, _ databasetype:="dBASE IV", _ databasename:=strDBPath, _ objecttype:=acTable, _ Source:=strDBName, _ Destination:=strTable, _ structureonly:=False

Assign the appropriate form as the subform's source object.

Me![subNewJobs].SourceObject = "fsubNewJobsDB" Case 2

Paradox strTable = "tblNewJobs"

DoCmd.TransferDatabase transfertype:=acImport, _ databasetype:="Paradox 4.X", _ databasename:=strDBPath, _ objecttype:=acTable, _ Source:=strDBName, _ Destination:=strTable, _ structureonly:=False

Assign the appropriate form as the subform's source object.

Me![subNewJobs].SourceObject = "fsubNewJobs" Case 3

Lotus 1-2-3

strTable = "tblNewJobs"

DoCmd.TransferSpreadsheet transfertype:=acImport, spreadsheettype:=acSpreadsheetTypeLotusWK3, _ TableName:=strTable, _ FileName:=strAppFileAndPath, _ hasfieldnames:=True

Assign the appropriate form as the subform's source object.

Me![subNewJobs].SourceObject = "fsubNewJobs"

End Select

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The "Save New Jobs to Table" button's event procedure sets up a Select Case statement to run one of three append queries to append the new Jobs data to the tblJobs table:

Private Sub cmdSaveJobs_Click()

On Error GoTo ErrorHandler

DoCmd.SetWarnings False intFileType = Nz(Me![fraFileType].Value, 1) Select Case intFileType Case 1

dBASE

DoCmd.OpenQuery "qappNewJobsDB" Case 2

Paradox

DoCmd.OpenQuery "qappNewJobsPdox" Case 3

Lotus 1-2-3

DoCmd.OpenQuery "qappNewJobsLotus" End Select strTitle = "Jobs imported"

strPrompt = "New jobs imported into tblJobs from " _

& GetAppFile() MsgBox strPrompt, vblnformation + vbOKOnly, strTitle

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The Source File button's event procedure opens a File Picker dialog for selecting an application file for importing, filtering for dBASE, Paradox, or Lotus files depending on the option selected on the form:

Private Sub cmdSourceFile_Click()

On Error GoTo ErrorHandler

Dim fd As Office.FileDialog Dim txt As Access.TextBox Dim strPath As String Dim strFilter As String

Create a FileDialog object as a File Picker dialog box.

Set fd = Application.FileDialog(msoFileDialogFilePicker) Set txt = Me![txtSelectedAppFile]

Set the initial path to the custom Input Documents path strPath = GetInputDocsPath()

intFileType = Nz(Me![fraFileType].Value, 1)

With fd

.title = "Select database or spreadsheet file with " _

& "job data to import" .ButtonName = "Select" .Filters.Clear

Select Case intFileType

Case 1

.Filters.Add "dBASE files", "*.dbf"

Case 2

.Filters.Add "Paradox files", "*.db" Case 3

.Filters.Add "Lotus 1-2-3 files", "*.wk3" End Select

.InitialView = msoFileDialogViewDetails .InitialFileName = strPath If .Show = -1 Then strAppFile = CStr(fd.SelectedItems.Item(1)) Else

Debug.Print "User pressed Cancel" End If End With txt.Value = strAppFile

Save the value to tblInfo.

SaveAppFile (strAppFile)

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

The "Import File Type" option group's After_Update procedure first checks that the selected application file is the right type, and clears it if not, then sets up a Select Case statement to process the three types of application files differently, calling the SaveTextFile Sub:

Private Sub fraFileType_AfterUpdate()

On Error GoTo ErrorHandler

Dim strExt As String

Check that selected application file is the right type, and clear the file selection if not.

intFileType = Nz(Me![fraFileType].Value, 1)

If Len(GetTextFile()) > 4 Then strExt = Right(strAppFile, 3) End If

Select Case intFileType Case 1

dBASE

If strExt <> "dbf" Then SaveAppFile ("")

Me![txtSelectedAppFile].Value = "" End If

Case 2

Paradox

If strExt <> ".db" Then SaveAppFile ("")

Me![txtSelectedAppFile].Value = "" End If

Case 3

Lotus 1-2-3

If strExt <> "wk3" Then SaveAppFile ("")

Me![txtSelectedAppFile].Value = "" End If

End Select

Me![subNewJobs].SourceObject = ""

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

After importing data from the legacy application file, it is now in an Access table, where you can work with it in the future.

0 0

Post a comment