![]() One thing that would be nice is if there was a way to figure out the name of the table that contains the paste errors, then it would be possible to know if there had been errors and to be able to tell the user what the errors were. This could cause import errors when importing data after the first row. One of the issues that could happen is that the TransferSpreadsheet is that, when creating a table, it will base a field type on the first entry, such that if the first entry is a number, than that column will be for numeric. This could be easily adapted for by including the other file types in the dialog filter, and having the TransferSpreadsheet arguments modified to handle the other types. This means that there cannot be empty cells in the data, at least where the scan occurs, which is diagonally and then right or down Īnother limitation is that this code is designed to work with only with spreadsheets that have been saved in newest format, and not “ CSV” data. Then there is a scan to find the last data row and column, and any empty cells will signal the code that the end of the data has been reached. This allows other information to be included in the spreadsheet such that it does not interfere with the transfer. It may be preferable to look for a specific String, or just assume that the data will start in the top left cell. This is just basic functionality, and you will probably want to modify this code to meet your specific needs.įirst, the first non- Empty Cell is searched for to find the start of the table so that the data does not have to start in the first row and column. When that completes, the table is referenced in the subform SourceObject, and the subform is made visible. Then the DoCmd.TransferSpreadsheet is executed. It returns the code for the extent of the cells found (e.g. ![]() This Function will search the cells of the Worksheet to the right and down for the last non- Empty Cell by first progressing diagonally right and down, and then either right or down to find the extent of the data. Sheet.cells(currentRow, currentColumn)).Address( False, False) While Not IsEmpty(sheet.cells(currentRow, currentColumn))ĬurrentColumn = currentColumn + 1 Wend If Not IsEmpty(sheet.cells(currentRow - 1, currentColumn)) ThenĬurrentRow = currentRow - 1 While Not IsEmpty(sheet.cells(currentRow, currentColumn))ĬurrentColumn = currentColumn - 1 ElseIf Not IsEmpty(sheet.cells(currentRow, currentColumn - 1)) ThenĬurrentColumn = currentColumn - 1 While Not IsEmpty(sheet.cells(currentRow, currentColumn))įindDataCells = sheet.range(sheet.cells(startRow, startColumn), Public Function FindDataCells(sheet As Worksheet, initalCell As String) As String Dim startRow, startColumn, currentRow, currentColumn As IntegerĬurrentColumn = sheet.range(initalCell).Column Next the Worksheet with the specified name is returned using the GetExcelWorksheet: SubFormData.Visible = True End If Set sheet = Nothing End Subīefore all this processing in the click event handler for the ListBox selection, the reference to the temporary table being used in the RowSource of the sub form is removed by setting the RowSource to Empty. SubFormData.SourceObject = " Table.T_Temp" TextBo圎xcelFileToImport, True, sheetRange SheetRange = listBoxWorksheets & " !" & rangeĭoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, " T_Temp", & " left corner of the contents to import" MsgBox " Could not find cell with content, which is the cell that should be the upper " _ Set sheet = GetExcelWorksheet(textBo圎xcelFileToImport, listBoxWorksheets)įirstCell = FindFirstNonEmpt圜ell(sheet, 10) The implementation of the Browse is done in the GetExcelFile method:ĭim firstCell As String Dim range As String Dim sheetRange As String Next the software must let the user select the Excel file to use. ![]() If a file is not selected, then the code returns, otherwise the Excel file is opened to find its sheet names, which are displayed in a list box to allow the user to select the sheet to import.įirst the information in the Form is cleared, including clearing out the file name TextBox, the sheet names ListBox, and the hide the sub form. This code first clears out information in the form that may be applicable to the last file that was selected, and then the user is provided a dialog to select a file with the “ xlsx” extension. ListBoxWorksheets.RowSource = itemsString ![]() ItemsString = Join(ExcelSheetsNameList(textBo圎xcelFileToImport), " ") If IsNull(textBo圎xcelFileToImport) Then Exit Sub ' Fill in list box that will contain the sheet names ' Allow user to browse to Excel file to import ' Clear out existing information before browse Dim itemsString As String ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |