Importing from an xlsx file via automation Part II

Last time I created an entry in weblogs about the automation approach of importing an xlsx file because with the change of format on Excel 2007 onwards, VFP is generating an error now on importing it.  Since importing has no problem on lower versions of Excel files, the solution I have proposed is to save the sheet to a lower version, preferably XL5 format for backward compatibility reason.

Now, a new question is raised.  Though that approach works fast and is easy, how can we import from a specific worksheet inside the excel workbook?

If you use the approach that I have shown, it will always get only the first sheet.  Here is another trick though so we can do a selective import of an existing sheet inside that workbook.  And as a bonus due to another question raised inside Foxite Forum, here is also a way on how to replace the column names with the contents of the first row so Field Names will be better and proper instead of Field Name A, B, C.... and so on:

Local lcFile, lcTemp, loExcel As Excel.Application, lnSheet, lnloop, lcOldName, lcNewName, lProceed

lcFile = Getfile("xlsx")

If !Empty(m.lcFile)   && check if not cancelled

      lcTemp = Addbs(Getenv("TEMP"))+Sys(3)+".xls"

      loExcel = Createobject("excel.application")

      With loExcel

            .DisplayAlerts=.F.  && somewhat similar to SET SAFETY OFF of VFP

            .Workbooks.Open(m.lcFile)

            lnSheet = Int(Val(Inputbox("Please select the Target Sheet: 1-"+Transform(.Sheets.Count),"Select Sheet","1")))

            If Empty(m.lnSheet)   && check if not cancelled

                  lProceed = .F.

            Else

                  lProceed = .T.

                  For lnloop = .Sheets.Count To 1 Step -1

                        If m.lnloop <> m.lnSheet

                              .Sheets(m.lnloop).Delete

                        Endif

                  Next

                  .ActiveWorkbook.SaveAs(m.lcTemp,39)  && Place it in the temp folder

                  .ActiveWindow.Close(.T.)

                  .Quit

            Endif

      Endwith

      If m.lProceed   && not Cancelled by user?

            Import From (m.lcTemp) Type Xls  && or XL5

            * Rename Field Names to better ones

            For lnloop = 1 To Fldcount()

                  lcOldName = Field(m.lnloop)

                  lcNewName = Strtran(Alltrim(Transform(&lcOldName)),' ','')

                  Alter Table Dbf() Rename Column &lcOldName To (m.lcNewName)

            Next

            Delete

            Browse Normal

            Close Databases All

            * Clean it up, delete temp xl5 file used

            Delete File (lcTemp)

      Else

            Messagebox("Aborted by user!",0+64,"Oppppssss!")

      Endif

Else

      Messagebox("Aborted by user!",0+64,"Oppppssss!")

Endif

The trick I have used above follows:

• Determine the number of sheets of the source excel file for importing
• Choose which sheet you wanted to import
• Delete the rest of the sheets not subject for importing
• Save it on a temporary file in the local OS user's temporary folde
• Import it to a table
• Get the content of the first record and change the field names with those
• Then finally delete the temporary file.

Of course, I am presuming that the first row of your excel sheet will contain possible valid Field Names later.  Since this is an immediate post (like most of my postings), I simply used STRTRAN() here for this tutorial but you can enhance that to really remove invalid characters.

Enjoy!