Replies

My code links the table then take each record and inserts it into other tables based on certain criteria.

I’ve tried formatting the Excel sheet column to text and I’ve tried putting the non-numeric values at the top, but to no avail.

This is the code I use:
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, “blahblah”, , True, “”

It’s right after this piece that I can check the linked table and see that the column has the wrong data type.

Sign me Very Frustrated!

Do all tabs have data in them, or do you need to check to see if a tab has data? I think probably Automation would be the best choice in trying to sort that out. For one thing you need to figure out how many tabs each workbook has. You will have to use the Excel object model to do both tasks, and the sheet property is exposed to automation.

Thanks for the pointer….it helped me figure it all out!

Do all tabs have data in them, or do you need to check to see if a tab has data? I think probably Automation would be the best choice in trying to sort that out. For one thing you need to figure out how many tabs each workbook has. You will have to use the Excel object model to do both tasks, and the sheet property is exposed to automation.

I believe all the tabs have data, but I can easily check to see if there is data in the cells.

Ok, I figured out the problem. It only took me, however, a day and a half.

To validate my numbers, I took the results of the query and copy/pasted them to Excel. I would then AutoSum the data to see the totals.

For some odd reason on one set of data in my cross tab, it worked fine, but in the second set, it included the header row, which was the date in YYYYMM format. It wasn’t until I compared the numbers to see what the difference was that I was able to see the pattern. One month was off my $200,810 the next by $200,811, the next by $200,812 and so on.

I could not believe that it was that simple…UGH!!!!!!!

Looks like it’s back to Excel Formulas 101 for me.

Some of us more often than we’d care to admit.

Create two queries. One to calculate the Min Start time by date and the other to calculate the Max End time by date. Link the two queries by date. See attached (qryMinMax shows the final results):

This worked like a charm, thanks!

Is there property setting or alternative way to stop the confirmation for an append?

I am parsing through thousands of records, building a string for each group, and inserting the info into a table.

I am running the following “insert into” in VBA:

strinsertsql = “INSERT INTO PubTable (MFBIndex, UFSiteString, SumUFSiteString, ” & _
“NumberUFSiteString ) ” & _
“values (” & strmfbidx & “, ” & “‘” & strpass & “‘” & “, ” & sumid & “, ” & count & “);”
DoCmd.RunSQL strinsertsql

I get a msgbox with each execution and don’t want to hit OK each time. Any better way to do this or just a property I can set. I can’t seem to find one on my own.

DoCmd.SetWarnings False before the insert
DoCmd.SetWarnings True after the insert

I even tried this

Code:

Function ExcelCheck2(strFileIn As String) As Boolean
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
  MsgBox "Excel is not running", vbInformation
Else
  MsgBox "Excel is running", vbInformation
End If

Set xlApp = GetObject(strFileIn)
If xlApp Is Nothing Then
  MsgBox "Workbook is not open", vbInformation
  ExcelCheck2 = False
Else
  MsgBox "Workbook is open", vbInformation
  ExcelCheck2 = True
End If
On Error GoTo 0
End Function

but never get a correct response on the workbook being open…

I modified your code to this

Code:

Function ExcelCheck2(strFileIn As String) As Boolean
	Dim xlApp As Object
		
	On Error Resume Next
	Set xlApp = GetObject(strFileIn)
	If xlApp Is Nothing Then
	  ExcelCheck2 = False
	Else
	  ExcelCheck2 = True
	End If

End Function

but it never returns a FALSE….

That’s almost exactly what I did, except I did not create a table. Instead I used the WHERE ID NOT IN (SELECT TOP 25….)

Worked like a charm!

Hi There

This is not the most elegant answer but it may get you out of a hole. I have created a dummy database with a table called tblList, in it were 10 records. From this table I created a table using the Make Table query or the following code:

I then created this bit of SQL
where id not in (select id from tblTop3);

It did produce the result….not elegant but could be a start of a solution

I love it when I can solve my own questions….

Sub ImportAllTabs()
    Dim strPath As String
    strPath = "C:PathToFiles"
    
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    Dim strFileName As String
    strFileName = Dir(strPath)
    While strFileName  ""
        Debug.Print strFileName
        'Open the spreadsheet and get the tab names...
        With objXL
            .Workbooks.Open Filename:=strPath & strFileName
            
            Dim arrSheetName() As String
            ReDim arrSheetName(objXL.Sheets.Count)

            For x = 1 To objXL.Sheets.Count
                arrSheetName(x) = objXL.Sheets(x).Name
            Next
        End With
        objXL.Workbooks.Close
        'Import the tabs from this spreadsheet
        For x = 1 To UBound(arrSheetName)
            Debug.Print vbTab & arrSheetName(x)
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableToImportTo", _
                strPath & strFileName, True, arrSheetName(x) & "$"
        Next x
        'Next spreadsheet
        strFileName = Dir
    Wend
    
    objXL.Quit
    Set objXL = Nothing
    
End Sub

She could not.

I did, however, find the problem. I had only added her under Sharing/Permissions. I added her under Properties/Security, and it worked like a charm.

I tested it by making a share on my PC for the back end. She gets the linked tables, because I can see the .ldb. There is no mention of read only until she saves, and this code fails

rst.addnew

See the picture for her permissions.

Hans, I tried your suggestion. Now when I try to open the query in VBA, I get the following error:
Run-time error 3061
Too Few Parameters. Expected 0.

This is the VBA in question:
Set rstDetails = db.OpenRecordset(“SELECT * FROM [qryReportA-PO_Count] WHERE BU = ‘” & rstBU!BU & “‘”, dbOpenForwardOnly)

Well that was simple enough…

Thanks!!