VBA - Switching between generic workbooks

[flipdazed]

Hi there,

I have a background in Fortran 95 programing and I'm trying to grasp the basics of VBA. I have managed to write a small program but I don't have enough VBA knowledge to figure out what is wrong

My problem is as follows:
I have multiple files with a set of questions with the answers "Y" or "N" in set cells. I want to run the same macro from each of these files which will return values of "1" or "0" to a master file with an IF statement.

1) The first part of my problem is identifying the filename of the file I currently open and referencing it in Workbooks() I haven't properly refenced my file name from from the dim rawaudit in the code:

[/I]Workbooks("RawAudit").Sheets("Sheet1").Select

2) The second part of my problem is the IF statement I have. I'm sure it isn't very efficient and it doesn't properly work either but the basics are there.

I've tried to colour in the code below to make it more readable. (The conditional file open part works perfectly as I copied it form the microsoft site)

Thanks a lot,

Alex.

[COLOR=blue]Sub[/COLOR] AuditMasterfile_DataTransfer()
[COLOR=seagreen]' RUN THIS MACRO INSIDE THE RAW AUDIT DATA FILE[/COLOR]
[COLOR=seagreen]' Transfers Data from Raw Audit File by opening MasterFile and transfering data _[/COLOR]
[COLOR=seagreen]into the transfer box. Outputs 1 for a Y and 0 for a No.[/COLOR]
 
[COLOR=seagreen]'Obtain name of Monthly Audit File[/COLOR]
[COLOR=blue]Dim[/COLOR] RawAudit [COLOR=blue]As String[/COLOR]
    RawAudit = ThisWorkbook.Name
 
[COLOR=seagreen]'Open Master File if not currently open - dim from microsoft[/COLOR]
[COLOR=blue]Dim [/COLOR]strFileName [COLOR=blue]As String[/COLOR]
    
[COLOR=seagreen]   ' Full path and name of file.[/COLOR]
    strFileName = "C:\Users\105057819\Documents\Monthly Sales Audit\ITO Audit Masterfile.xlsx"
    [COLOR=seagreen]' Call function to test file lock.[/COLOR]
    [COLOR=blue]If Not[/COLOR] FileLocked(strFileName) [COLOR=blue]Then[/COLOR]
   [COLOR=seagreen]' If the function returns False, open the document.[/COLOR]
    Workbooks.Open strFileName
   [COLOR=blue]End If[/COLOR]
    
[COLOR=seagreen]'Goto Raw Audit File and Extract all data to Masterfile with 1 or 0 depending on Y/N[/COLOR]
    Workbooks("RawAudit").Sheets("Sheet1").Select
 
  [COLOR=seagreen]  'Q1[/COLOR]
    [COLOR=blue]If[/COLOR] Range("B9") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K23") = "1"
    [COLOR=blue]ElseIf [/COLOR]Range("B9") = "N"[COLOR=blue] Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K23") = "0"
    [COLOR=blue]End If[/COLOR]
[COLOR=seagreen]   'Q2[/COLOR]
    [COLOR=blue]If [/COLOR]Range("B10") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K24") = "1"
    [COLOR=blue]ElseIf[/COLOR] Range("B10") = "N" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K24") = "0"
    [COLOR=blue]End If[/COLOR]
 [COLOR=seagreen]   'Q3[/COLOR]
    [COLOR=blue]If[/COLOR] Range("B11") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K25") = "1"
    [COLOR=blue]ElseIf[/COLOR] Range("B11") = "N" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K25") = "0"
[COLOR=blue]   End If[/COLOR]
   [COLOR=seagreen]'Q4[/COLOR]
    [COLOR=blue]If[/COLOR] Range("B12") = "Y" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K26") = "1"
    [COLOR=blue]ElseIf[/COLOR] Range("B12") = "N" [COLOR=blue]Then[/COLOR]
        Workbooks("Monthly Sales Audit.xlsx").Sheets("Data").Range("K26") = "0"
  [COLOR=blue]  End If[/COLOR]
   
[COLOR=blue]End Sub[/COLOR]
 
 
[COLOR=green]'Open Masterfile if not already open - function from microsoft[/COLOR]
[COLOR=blue]Function[/COLOR] FileLocked(strFileName [COLOR=blue]As String[/COLOR]) [COLOR=blue]As Boolean[/COLOR]
   [COLOR=blue]On Error Resume Next[/COLOR]
[COLOR=green]  ' If the file is already opened by another process,[/COLOR]
[COLOR=green]  ' and the specified type of access is not allowed,[/COLOR]
[COLOR=green]  ' the Open operation fails and an error occurs.[/COLOR]
   [COLOR=blue]Open[/COLOR] strFileName [COLOR=blue]For Binary Access Read Write Lock Read Write As[/COLOR] #1
   [COLOR=blue]Close [/COLOR]#1
  [COLOR=green] ' If an error occurs, the document is currently open.[/COLOR]
   [COLOR=blue]If[/COLOR] Err.Number <> 0 [COLOR=blue]Then[/COLOR]
    [COLOR=green]  ' Display the error number and description.[/COLOR]
      [COLOR=blue]MsgBox[/COLOR] "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   [COLOR=blue]End If[/COLOR]
[COLOR=blue]End Function[/COLOR]

 

[Andrew Poulsom]

You can assign the workbook to an object variable like this:

Dim wb As Workbook
Set wb = Workbooks.Open(strFileName)

Thereafter you can use wb to refer to it.

 

[RoryA]

I would use a loop:

Sub AuditMasterfile_DataTransfer()
' RUN THIS MACRO INSIDE THE RAW AUDIT DATA FILE
' Transfers Data from Raw Audit File by opening MasterFile and transfering data _
  into the transfer box. Outputs 1 for a Y and 0 for a No.

'Obtain name of Monthly Audit File
   Dim wbkRawAudit       As Excel.Workbook
   Dim wbkSalesAudit     As Excel.Workbook
   Dim strFileName       As String
   Dim rngCell           As Excel.Range
   Dim lngRow            As Long

   Set wbkRawAudit = ThisWorkbook

   'Open Master File if not currently open - dim from microsoft

   ' Full path and name of file.
   strFileName = "C:\Users\105057819\Documents\Monthly Sales Audit\ITO Audit Masterfile.xlsx"
   ' Call function to test file lock.
   If Not FileLocked(strFileName) Then
      ' If the function returns False, open the document.
      Set wbkSalesAudit = Workbooks.Open(strFileName)

      'Goto Raw Audit File and Extract all data to Masterfile with 1 or 0 depending on Y/N
      For lngRow = 9 To 12
         ' assign output cell in column K of target workbook, 14 rows below the source cell
         Set rngCell = wbkSalesAudit.Sheets("Data").Range("K" & lngRow + 14)
         
         Select Case wbkRawAudit.Sheets("Sheet1").Range("B" & lngRow).Value
            Case "Y"
               rngCell.Value = 1
            Case "N"
               rngCell.Value = 0
            Case Else
               rngCell.ClearContents
         End Select
      Next lngRow
   End If

End Sub

 

[flipdazed]

Hi Rory,

Thanks a lot for taking your time to read this, I tried using the bit of code you edited. I think I've understood the processes you've used, however, it returns "out of range" at:

Select Case wbkRawAudit.Sheets("Sheet1").Range("B" & lngRow).Value

I've checked the file assigned to wbkRawAudit and it definitely has "Sheet1" is there an error with the assignment of the filenames to the variables?


Alex.

 

[RoryA]

If you are getting a subscript out of range error, then the workbook with the code does not contain a sheet called "Sheet1".

 

[flipdazed]

Hi Rory

If the macro is saved in PERSONAL.xlsb then will "ThisWorkbook" refer to the workbook the macro is saved in?

I also seem to be getting an error if the masterfile is already opened. The masterfile has an unchanging filename is there any way in VBA to go:

IF (file) = Not Open Then
Open File
Else IF File = Open
Continue
End IF

Alex

 

[RoryA]

Yes, ThisWorkbook always refers to the workbook containing the code. You may need ActiveWorkbook instead.

Try this code (untested):

Sub AuditMasterfile_DataTransfer()
' RUN THIS MACRO INSIDE THE RAW AUDIT DATA FILE
' Transfers Data from Raw Audit File by opening MasterFile and transfering data _
  into the transfer box. Outputs 1 for a Y and 0 for a No.

'Obtain name of Monthly Audit File
   Dim wbkRawAudit       As Excel.Workbook
   Dim wbkSalesAudit     As Excel.Workbook
   Dim strFileName       As String
   Dim strFilePath       As String
   Dim rngCell           As Excel.Range
   Dim lngRow            As Long

   Set wbkRawAudit = ThisWorkbook

   'Open Master File if not currently open - dim from microsoft

   ' Full path and name of file.
   strFilePath = "C:\Users\105057819\Documents\Monthly Sales Audit\"
   strFileName = "ITO Audit Masterfile.xlsx"

   ' check if file already open
   If IsWorkbookOpen(strFileName) Then
      Set wbkSalesAudit = Workbooks(strFileName)
   Else
      ' Call function to test file lock.
      If Not FileLocked(strFileName) Then
         ' If the function returns False, open the document.
         Set wbkSalesAudit = Workbooks.Open(strFilePath & strFileName)
      End If
   End If
   If Not wbkSalesAudit Is Nothing Then
      'Goto Raw Audit File and Extract all data to Masterfile with 1 or 0 depending on Y/N
      For lngRow = 9 To 12
         ' assign output cell in column K of target workbook, 14 rows below the source cell
         Set rngCell = wbkSalesAudit.Sheets("Data").Range("K" & lngRow + 14)

         Select Case wbkRawAudit.Sheets("Sheet1").Range("B" & lngRow).Value
            Case "Y"
               rngCell.Value = 1
            Case "N"
               rngCell.Value = 0
            Case Else
               rngCell.ClearContents
         End Select
      Next lngRow
   End If

End Sub

Public Function IsWorkbookOpen(strName As String) As Boolean
   Dim wbk As Workbook
   On Error Resume Next
   Set wbk = Workbooks(strName)
   IsWorkbookOpen = Not (wbk Is Nothing)
End Function
Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
  ' If the file is already opened by another process,
  ' and the specified type of access is not allowed,
  ' the Open operation fails and an error occurs.
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the document is currently open.
   If Err.Number <> 0 Then
      ' Display the error number and description.
      MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function

 

[flipdazed]

Hi Rory,

It's returning a path/file access error, no debug option and not opening the masterfile anymore.

Is there an error in:

Public Function IsWorkbookOpen(strName As String) As Boolean
   Dim wbk As Workbook
   On Error Resume Next
   Set wbk = Workbooks(strName)
   IsWorkbookOpen = Not (wbk Is Nothing)
End Function

I don't know enough VBA language but the rest seems to follow on logically to each part


Alex

 

[RoryA]

I missed a bit - change this line:

If Not FileLocked(strFileName) Then

to this:

If Not FileLocked(strFilePath & strFileName) Then

 

[flipdazed]

Works perfectly. Thanks Rory! Thanks for all your help on this problem I've learned a lot from it.

Just wondering, does VBA discriminate between upper and lower case letters. In otherwords if I have a lower case "y"/"n" instead of a "Y"/"N" will it fail?

Alex