VBA to ImportSpreadsheet

[kwooten]

I have 56 .CIF files I need to import into a table. The data file has a bunch of stuff at the top that I do not need so my data really begins on the 10th row. The file is tab delimited with " text qualifier.. Does anyone have VBA I can piggyback from that would allow me to import, starting on row 10, a tab delimited file?

[June7]

If the workbook has a defined range, could possibly use TransferSpreadsheet. Otherwise, I expect requires Excel automation VBA code. A good site for examples is http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm. Adapt code to meet your requirements.

[kwooten]

If I am running Windows 10 and Excel 2016 and Access 2016 then what do I change this to? Does it matter if the files are .CIF?

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

[June7]

Sorry, I should have read question more carefully. I saw the title with 'Import Spreadsheet' and pretty much overlooked the other info.

So it is really a text file? DoCmd.TransferText will import text file but it won't allow skipping rows. Are the first 9 rows also in the tab delimited structure? Is the data all text or number type?

Might have to use VBA code to open text file object, read each line, parse the string to an array and feed the array elements to table fields. A lot of looping structures. Many examples of this available.

[John_G]

If I am running Windows 10 and Excel 2016 and Access 2016 then what do I change this to? Does it matter if the files are .CIF?

If the file names have the extension .CIF, then your code certainly won't work because it is looking for .xls (Excel) files.
Did you verify that the files are reasonably normal text files that you can open with WordPad for example?

If they are readable, then I think you will have to do as June suggested, which is to open them as a text file and read them line by line.

[kwooten]

I have attached an example of one of our CIF files. The header starts on row 8 and the data on row 10. The attachment is a basic file, I have others with 30+ rows but those have sensitive data in them.

[John_G]

I don't know what application the .CIF files come from, but it isn't Excel. But since they are plain text, you can work with them. The first 7 lines document the source system and the parameters used when exporting, and you don't need them. Line 8 is the list of fieldnames and will be very useful (see below).
The lines starting with DATA and ENDOFDATA can be discarded.

The problem with the data lines is that they are comma-delimited AND the values are all in quotation marks, even the numeric ones. The result is that when they are imported, everthing is short text. But I gave it a try, and it worked perfectly.

If this is just a one-time thing, and you don't mind doing a little editing, here is what I did with the test file you supplied.

1. Rename the file from .cif to .txt (no harm done )
2. Open the file in Notepad, or some other application that works well with text files
3. Delete lines 1 - 7
4. On the fieldnames line, Delete FIELDNAMES:
5. Delete the DATA and ENDOFDATA lines
6. Save the file

Now, in MS Access:

1. From the Ribbon, click External Data
2. From the New Data Source dropdown, Select From File, then Text File
3. Browse to select the text file, and click "Open"
4. Click OK
5. On the next screen, leave the selection as comma-delimited, and click Next
6. The next screen is the important one. Select the option "First Row Contains Field Names". Then, in the Text Qualifier dropdown, select ". The preview will be changed to reflect those two options.
7. Click Next twice (skips the field options part)
8. At the next screen, choose how Access will handle the PK (I used No Primary Key for testing)
9. Click Next to select a table name (I went with the default from the file name)
10. Click Finish to create the new table

The first part could be automated with a just a bit of VBA, to read the original .cif file, and write the edited .txt file. You could fancy it up with a Sub to fix all 56 .cif files in one go.

I don't know to what extent the second part can be automated in VBA, because I've never done that. It probably can be, if I can figure all the parameters out.

But your task is not as bad as it looked - let us know how it goes.

Update: Yes part 2 can be automated, subject to a couple of constraints. You would run an import manually as above, save the import specifications (option at the end of that process), and then do something like docmd.RunSavedImportExport "ImportName".

Now if you are really ambitious, you could write one sub to do all of that (both parts) all at the same time. (!)

[kwooten]

I was trying to avoid manually adjusting 56 different files and importing them individually. If I have to delete the rows I don't need then that is fine I can get someone to do that but would still like to have something I can use to import all files at once.

[kwooten]

Not sure if it helps but I was able to put some VBA into excel to combine all the worksheets then i was easily able to delete the top rows I do not need.. Now that I have these catalogs all in one workbook, different tabs, is it now easier to write VBA code to get them all into one table? The data is still comma separated and also has the quotations..

[John_G]

Is the data to be imported in an Excel Workbook with multiple worksheets, in multiple .cif files, or both? VBA can be used for either one - do you have a preference?

[kwooten]

I had the workbook separated so everything is in their own respective columns and saved as .xlsx

I tried using this code but I get an error from excel that says Sorry, we couldn't open 'H:\Ariba\Catalogs\CatalogFiles.xlsx\ I click ok then another box appears that says Run Time Error 1004

Does not Exist
used by another Program
has the same name

Option Compare DatabasePrivate Sub CatalogImport()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String, strTable As String
Dim strPassword As String


' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set objExcel = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0


' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True


' Replace C:\Filename.xls with the actual path and filename
strPathFile = "H:\Ariba\Catalogs\CatalogFiles.xlsx\"


' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "Catalogs"


' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "vbNullString"


blnReadOnly = True ' open EXCEL file in read-only mode


' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
      strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
      colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount


' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing


' Import the data from each worksheet into the table
For lngCount = colWorksheets.Count To 1 Step -1
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
            strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
Next lngCount


' Delete the collection
Set colWorksheets = Nothing


' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile
End Sub

[kwooten]

Is the data to be imported in an Excel Workbook with multiple worksheets, in multiple .cif files, or both? VBA can be used for either one - do you have a preference?

The data is in one spreadsheet with multiple workbooks and saved as a xlsx

[John_G]

Sorry, we couldn't open 'H:\Ariba\Catalogs\CatalogFiles.xlsx\

You have a backslash after the file name. Take it out and see if that works.

[kwooten]

well that worked..