Hello there everyone.
As the title suggests, I'm trying to populate a subform with information taken from a temporary table (pretty much like a datagridview on vb.net)
Within runtime, after the user selects an Excel file from the Office.Dialog object, all the columns are written into a temporary table to create the fields.
After the fields have been created, I fill them with the values that are read from all of the rows of the excel file.
Finally, after having the temporary table built I use it as the record source for a subform.
Below is the code for all I've described:
Code:
Private Sub btnBrowse_Click() Dim varItem As Variant
Dim f As Office.FileDialog
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Filters.Count > 0 Then
f.Filters.Clear
End If
f.Filters.Add "Todos os ficheiros", "*.*"
f.Filters.Add "Ficheiros Excel", "*.xlsx"
f.Filters.Add "Ficheiros Excel (Versão 97-2003)", "*.xls"
Dim fso As New FileSystemObject
Dim fileName As String
'FileDialog shows up for the user. User selects the Excel file to import.
If f.Show Then
If f.SelectedItems.Count = 1 Then
For Each varItem In f.SelectedItems
fileName = Left(fso.GetFileName(varItem), (InStrRev(fso.GetFileName(varItem), ".", -1, vbTextCompare) - 1))
If fileName = "Geracao Etiquetas Caixa SB" Then
strFile = fso.GetFileName(varItem)
strFilePathTmp = fileName & "_" & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "." & fso.GetExtensionName(varItem)
strFileToExport = fso.GetFile(varItem)
fileSelected = True
Exit For
Else
MsgBox "O nome do ficheiro escolhido não é valido." & vbCrLf & "O ficheiro deve conter o seguinte nome: Geracao Etiquetas Caixa SB", vbOKOnly, "Erro: Importação de Ficheiro"
End If
Next varItem
Else
MsgBox "É necessário seleccionar um ficheiro", vbOKOnly, "Importar Ficheiro"
End If
Else
Exit Sub
End If
'Access will now read the Excel file and create the temporary table from it (columns and rows).
Dim db As DAO.Database
Dim tmpTbl As TableDef
Dim tmpTblFld As Field
Set db = CurrentDb
Set tmpTbl = db.CreateTableDef("Tabela_Temp_Etiq_Caixa")
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
Dim xlWorkBook As Excel.Workbook
Set xlWorkBook = xlApp.Workbooks.Open(strFileToExport)
Dim xlWorkSheet As Excel.Worksheet
Set xlWorkSheet = xlWorkBook.Worksheets(1)
Dim xlColumnIndex As Integer
Dim xlRowIndex As Integer
For xlColumnIndex = 1 To 4
If xlColumnIndex = 1 Then
Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbText, 10)
ElseIf xlColumnIndex = 2 Then
Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbText, 40)
ElseIf xlColumnIndex = 3 Then
Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbLong)
Else
Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbLong, 13)
End If
tmpTbl.Fields.Append tmpTblFld
tmpTbl.Fields.Refresh
Next xlColumnIndex
For xlRowIndex = 2 To xlWorkSheet.UsedRange.Rows.Count
DoCmd.RunSQL "INSERT INTO " & tmpTbl.Name & " (Cod_Artigo, Descricao, Quantidade, EAN) VALUES " & _
"('" & xlWorkSheet.Cells(xlRowIndex, 1).Value & "', '" & xlWorkSheet.Cells(xlRowIndex, 2).Value & "', " & xlWorkSheet.Cells(xlRowIndex, 3).Value & _
", " & xlWorkSheet.Cells(xlRowIndex, 4).Value & ")"
Next xlRowIndex
'At this point I'll define the RecordSource property of the Subform.
'dgvBoxLabelsToPrint is the name of the Subform.
Me.dgvBoxLabelsToPrint.Form.RecordSource = "SELECT * FROM Tabela_Temp_Etiq_Caixa"
DoCmd.GoToControl "txtXlsFilePath"
Me.txtXlsFilePath.Text = strFileToExport
Me.btnGenerate.Enabled = True
DoCmd.GoToControl "btnGenerate"
End Sub
Now, my problem is that Access, for some reason that I'm not able to understand isn't recognizing the temporary table that is created on run-time, giving out the following error:
- Could not find output table 'Tabela_Temp_Etiq_Caixa'
This error occurs on the following block of code:
Code:
For xlRowIndex = 2 To xlWorkSheet.UsedRange.Rows.Count DoCmd.RunSQL "INSERT INTO " & tmpTbl.Name & " (Cod_Artigo, Descricao, Quantidade, EAN) VALUES " & _
"('" & xlWorkSheet.Cells(xlRowIndex, 1).Value & "', '" & xlWorkSheet.Cells(xlRowIndex, 2).Value & "', " & xlWorkSheet.Cells(xlRowIndex, 3).Value & _
", " & xlWorkSheet.Cells(xlRowIndex, 4).Value & ")"
Next xlRowIndex
I checked the immediate window for the tmpTbl.Name propery and it does recognize it, with the name of the table Tabela_Temp_Etiq_Caixa.
What am I missing here?