+ Reply to Thread
Results 1 to 4 of 4

Windows 7 :Issue with Exel VBA in Window 7

  1. #1
    Registered User
    Join Date
    04-23-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    1

    Issue with Exel VBA in Window 7

    Some of the macro is no longer working in Window 7. For example, the following code need to modified in a way that's acceptable by Window 7. It get stuck in "Set db = OpenDatabase(strSourceFile, False, True, strOptions)"



    Private Sub RunSQL(strSourceFile As String, strSQL As String, booHeader As Boolean, rgeTarget As Range, Optional shtTarget As Worksheet)
    Dim db As DAO.Database, rs As DAO.Recordset, f As Integer, r As Long, strOptions As String
    If rgeTarget Is Nothing Then GoTo CleanupRunSQL

    strOptions = "Text;"
    If LCase(Right(strSourceFile, 4)) = ".xls" Then strOptions = "Excel 8.0;"
    strOptions = strOptions & "HDR="
    If booHeader = False Then
    strOptions = strOptions & "No;"
    Else: strOptions = strOptions & "Yes;"
    End If

    On Error Resume Next

    If strSourceFile = ThisWorkbook.FullName Then
    Application.DisplayAlerts = False
    strSourceFile = ThisWorkbook.path & "\" & "tempdao.xls"
    ThisWorkbook.SaveCopyAs strSourceFile
    Application.DisplayAlerts = True
    End If

    Set db = OpenDatabase(strSourceFile, False, True, strOptions)
    On Error GoTo 0

    If db Is Nothing Then
    MsgBox "Error: Can't find the source file:" & vbCrLf & vbCrLf & _
    strSourceFile, vbExclamation
    GoTo CleanupRunSQL
    End If

    On Error Resume Next
    Set rs = db.OpenRecordset(strSQL)
    On Error GoTo 0

    If rs Is Nothing Then
    MsgBox "NOTE: No records match the data extract criteria.", vbInformation
    GoTo CleanupRunSQL
    End If

    RStoDestination rs, rgeTarget, shtTarget

    CleanupRunSQL:

    On Error Resume Next
    Set rgeTarget = Nothing
    Set shtTarget = Nothing
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    Kill ThisWorkbook.path & "\" & "tempdao.xls"
    On Error GoTo 0

    End Sub

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,205

    Re: Issue with Exel VBA in Window 7

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    10-09-2012
    Location
    dhaka
    MS-Off Ver
    Office 7
    Posts
    1

    Re: Windows 7 :Issue with Exel VBA in Window 7

    I have an Excel VBA macro Windows 7 from Windows XP I tried to switch. Both 32-bit versions of the Windows operating system. My Windows 7 Enterprise Edition version.
    The VBA code works fine on Windows XP. When I have tried to run the code on Windows 7, it crashes and reports no error description.

    I have registered msinet.ocx, which appears to be required for Microsoft Internet Transfer Control 6.0. I have also tried to fix security issues by adding the additional com components tabctl32, comctl332/232/32 and comdlg32, all of which are ocx files. However, nothing changes - my code still fails on Windows 7.

    Once msinet was unregistered, Excel VBA opens my Workbook, reports the missing reference msinet and does not work at all. How do I resolve this issue?

    cost segregation
    Last edited by jewelreja; 10-09-2012 at 01:42 PM.

  4. #4
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,207

    Re: Windows 7 :Issue with Exel VBA in Window 7

    jewelreja,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts