Importing an excel file in Access via VBA

[cgrider]

I have an access databae that I am creating to import data from the end user from an excel file. And from that generate a report for him to send to a customer. The issue that I am having the report has a group by section based off one of the fields and the field is based off a custom list of options. I.E. not numerical or alphebetical. So what I am trying to do is convert the values of the custom list to a numerical list to provide for easy grouping in the report. And I am having trouble doing this. I can import the data directly but I cant change it on the fly. I was thinking of using a SQL script to input into the table but I dont know how to loop through the excel file to import it into the table. is there a better way to do this or could some one give me some advice on how to do this. the output sql would look somethign like this

select data1, IIF(data2="somevalue",0,data2="somevalue",1,2) as data2 from <excel file>

any help would be greatly appreciated.

CG

[MMcCarthy]

Hi CG,

Try this ...

select data1, IIf(data2="somevalue",0,IIf(data2="somevalue",1,2)) as data2 from <excel file>
 

[cgrider]

Hi CG,

Try this ...

select data1, IIf(data2="somevalue",0,IIf(data2="somevalue",1,2)) as data2 from <excel file>
 

Thank you for the response I miss- typed my statement what I am having problems with is stepping through the excel file record by record. the way I am doing the excel import now is using the docmd.importexcel command built into access which does not allow for me to convert values on the fly.


BTW i am using Access 2003

[Denburt]

If you view this thread it may help solve your problem using a schema you can change the values to one type or another then make any further adjustments once in the database table.

http://www.thescripts.com/forum/thre...2800-2-10.html

Now if you truly want to open excel and scroll line by line I can post that as well. I hope you know a little about coding though since this is a bit more complicated. You will need to set a reference (Tools, Reference) in your VBA window to Microsoft Excel. This will give you the most control however there is also one below that does not require the Excel reference and they are similar in nature.

  Dim objExcelApp As Excel.Application 
    Dim objWorkbook As Excel.Workbook 
    Dim objWorksheet As Excel.Worksheet 
 
    strPath = ThisWorkbook.Path 'sets the path of the file for use later
 
    Set objExcelApp = New Excel.Application 'sets object as new instance of excel
 

No reference needed in your references:

Private Sub Command0_Click()
Dim i As String
Dim ACell As String
Dim Count As Integer, Count1 As Integer, Count2 As Integer
Dim objXL As Object
Dim objXLWB As Object
Dim objXLWS As Object
Dim MyBook As String
Dim strRange As String
Dim ObjSht As Object
    'MyBook = "C:\MyFolder\MyExcelSheet.xls" ' change to name/path of your workbook
    MyBook = "C:\MyFolder\MyExcelApp.xls"
    Set objXL = CreateObject("Excel.Application")
    Set objXLWB = objXL.workbooks.Open(MyBook)
    Set ObjSht = objXLWB.ActiveSheet
 
Dim EndCell As Integer
 
Count = 1
With ObjSht
ACell = .Cells(Count, 2)
    Do Until ACell = "" And Count2 > 5
     'Debug.Print Cells(Count, 2)
     If .Cells(Count, 2) = "" Then
     .Cells(Count, 2).Select
         Count2 = 0
     Count1 = 2
    ' Debug.Print Cells(Count, 1).Select
     Do Until .Cells(Count, Count1) > "" Or Count2 > 5
         'Debug.Print Cells(Count, Count1)
         If .Cells(Count, Count1) > "" Then Exit Do
         If Count1 > 11 Then
             If .Cells(Count, 2) = "" Then
                 .Range("B" & Count & ":L" & Count).Delete
             Else
                 Count = Count + 1
             End If
              Count1 = 2
             Count2 = Count2 + 1
             'Count = Count - 1
             'Exit Do
         End If
 
 
     Count1 = Count1 + 1
    Loop
End If
'If count2 > 5 Then Exit Do
ACell = .Cells(Count, 2)
Count = Count + 1
Loop
End With
End Sub
 

This is a smipet of some code I used at one time I think it verifies empty rows and deleted them as it scrolled down once it hit a certain number of blank cells it quit. You will need to refine it as you see fit. Any questions comments let me know.

[cgrider]

thank you very much for the code I will look at it tonight and try to make it work I do appreciate it. I will try both types and let you know on the results

I know a little bit of coding but by no means an expert which was why it was frustrating me so much I know what I wanted it to do just could not figure out how to do it :)

[cgrider]

could you show me the code with the reference to ms excel also? I am a little confused on the code you posted on the cell fields. does the excel file, once loaded, is treated like a table and I can access the record as a whole and access the headers as oppsed to the individual cells? or do I have restrict the user on moving columns around on the excel file. the reason why I am asking was because I was thinking of using the built in function of loading the execl to a tmp table and then running an update query against it to change the values and then copy the new data to the main table and wanted to see if I any of the steps can be trimmed out? any help would be appreciated. I am probably making things harder than they need to be. :) I seem to do that alot. Also what I want to do is use aliases for the field names so the end user can better undersatnd the column headings for when they put in the data and wont be confused with my naming conventions of the table coulmn names.


anyways thanks for your help on this I really do appreciate it.

[Denburt]

using the built in function of loading the execl to a tmp table and then running an update query against it to change the values and then copy the new data to the main table and wanted to see if I any of the steps can be trimmed out?

Probably your best bet.

I am probably making things harder than they need to be. I seem to do that alot.

Me too.

what I want to do is use aliases for the field names so the end user can better undersatnd the column headings for when they put in the data and wont be confused with my naming conventions of the table coulmn names.

Umm need more coffee will get back to you on this. :)

[cgrider]

ok this is the code that I wrote if you can take a look at it and see if I am on the rigth track or not I did not add all the colums names from the tables to the code here to make it a little more readable. I hope this might help better explain what i am trying to do.

Private Sub btnEnterWarranty_Click()
 
Dim strMessage As String
Dim vrtSelectedItem As Variant
Dim strMyDir As String
Dim strTable As String
Dim strTmpTable As String
Dim strSQL As String
Set dlgopen = Application.FileDialog(msoFileDialogFilePicker)
 
strTmpTable = "tblTmpData"
strTable = "tblwarranty"
 
strForm = "frmWarrantyInput"
strMessage = "Do you have a excel file to import?"
'============================================================
 
 
 
'=================================================================
 
If MsgBox(strMessage, vbYesNo, "External Data") = vbYes Then
'This is the import of the excel file section
 
With dlgopen
 
    .Title = "Import Warranty Information Data"
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xls", 1
 
    If .Show = True Then
        For Each vrtSelectedItem In .SelectedItems
        strMyDir = CStr(vrtSelectedItem)
        ' MsgBox strMyDir
 
        Next vrtSelectedItem
 
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTmpTable, strMyDir, True
        strSQL = "update " & strTmpTable & " set pinstalled = iif('installed',1,iif('Spare',2,3))"
        DoCmd.RunSQL strSQL
        strSQL = "insert into " & strTable & " from (select [service bulletin number] as location, pinstalled from " & strTmpTable
        DoCmd.RunSQL strSQL
 
        MsgBox "The Import is complete"
    Else
        MsgBox "You clicked Cancel"
    End If
End With
 
Else
    DoCmd.Close
    DoCmd.OpenForm strForm
End If
 
 
End Sub
 

any suggestions are always welcomed

[Denburt]

I think it is looking good however "Next vrtSelectedItem" should follow your transfer method in case they selected more than one file in the dialog box.

        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTmpTable, strMyDir, True
        strSQL = "update " & strTmpTable & " set pinstalled = iif('installed',1,iif('Spare',2,3))"
        DoCmd.RunSQL strSQL
        strSQL = "insert into " & strTable & " from (select [service bulletin number] as location, pinstalled from " & strTmpTable
        DoCmd.RunSQL strSQL
 Next vrtSelectedItem