Text Import Specification Regardless Number of Fields

[kjv1611]

This may be a simple answer, or it may just not be possible - at least the way I want to do it.

I have 92 worksheets in one workbook which I have been importing into Access, and combining into one table.

The first time I did this, I had a LOT of manual effort to do in order to accomplish this. Reason being, all the sheets have similar formats, and most are the same, but a few are varied. And besides that, they all have blank records, and some of them have a blank record as their first record/row.

So, I've been working through some VBA to take care of all the possible variations, or all that I can think of anyway.

For these variations, I would like to use a import delimiter that will import every field as text from every text file (I'm exporting as tab delimited from Excel to get over a few of the field formatting issues I've had).

The problem is that not every sheet/file/table has the same number of columns/fields.

So, is there a way to create just a generic "all text format" specification?

I'm hoping it's just a simple thing that I'm overlooking, and that it doesn't require further programming.

If necessary, though, I'll get all that information from Excel for each sheet, and use that to set the field formats...

--------

Of course, another thought I've had, altogether different is to just import the data into the same tables each time if the table exists in order to get around that... however, I'll probably still need to keep the option for a "new" spreadsheet in case a new one is inserted one month.

Any thoughts/suggestions/references/examples greatly appreciated.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, maybe I don't like the linking method all that much. And perhaps I did something incorrectly, but I edited your code, and in my opinion, this runs really slow. I'll try canceling the current run, changing the parts that I edited, and restart.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, I tried changing it, but nope it's just slow. Of course the other method wasn't super speedy, but I think it worked a little faster for some reason. I'm guessing that somehow the whole linking thing is running slower than just importing the data... I changed the couple items back to the way I originally tried it.

Here's my code:

Option Compare Database
Option Explicit

Private Sub LinkInMethod()
    Dim strFileName As String
    Dim ObjXL As Excel.Application
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim x As Integer
    
    Set ObjXL = CreateObject("Excel.Application")
    ObjXL.Visible = True
    
    strFileName = "G:\Analytics\MyWorkbook.xls"
    Set wb = ObjXL.Workbooks.Open(strFileName)
    x = 0
    For Each ws In wb.Worksheets
        If InStr(ws.Name, "Intro") Then
        ElseIf InStr(ws.Name, "Terms") Then
        Else
            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, ws.Name, strFileName, True, ws.Name & "$"
            x = x + 1
        End If
    Next ws
    MsgBox x & "Worksheets imported!", vbInformation, "Operation Complete"
    
    
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Alright, I almost have a working method. However, I've come to yet another error. To get around this error, I am going to need some help from a reference or some code to find the answer.

So, without further ado, here is what I am doing:
[OL]
[LI]Deleting previously existing tables to be sure all is clean.[/LI]
[LI]Creating a table based off each Worksheet[/LI]
[LI]Importing the worksheet into its now existing table.[/LI]
[/OL]
The code seems to run pretty quickly now, but I've got one problem. Apparently, for some reason, I can create a field name with "funny" characters to Access, but I cannot import fields that way???

So to get around this, I believe that what I need to do is clear out all "funny" characters on the spreadsheet before I do anything in creating the table and importing it.

Can anyone offer a reference as to all the funny characters that are not allowed in Access, or to some variable type or library in Access that will take care of that for me?

Thanks for all the thoughts and help so far!

--

"If to err is human, then I must be some kind of human!" -Me