Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Excel from an ACCESS MODULE
I ahve tried posting this in the Access Module forum buit they told me to try here. Please note that I writing the VB code in an ACCESS Module not directly in EXCEL... Thanks in advance for your help! Here's a problem I have been stuck on for the last three months. I have a lot of VBA code in an access module that creates queries and tables. At the end of the code, I transfer all this information to an excel spreadsheet using the transfer spreadsheet option. I then have a batch of code which opens the spreadsheet, formats it, saves it and closes it. It works like a dream. However, the code is currently telling the excel spreadsheet to select collumns K and L and turn the whole columns pink. What I actually want is the code to search through columns K and L and turn an individual cell pink if the value is NO. Does this mean I have loop around each cell? How do I do this - i cant find any otherexamples on the net!! Any help or suggestions greatly appreciated. Here's the current code: Dim xlApp As Object Dim StrPath As String StrPath = "H:\Census_Checking\FSM_TO_BE_CHECKED.xls" Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True With xlApp ..Workbooks.Open Filename:=StrPath ..Columns("K:L").Select With .Selection ..Interior.ColorIndex = 7 End With End With With xlApp ..Cells.Select ..Cells.EntireColumn.AutoFit ..Rows("1:1").Select ..Selection.Font.Bold = True End With xlApp.Application.ActiveWorkbook.Save xlApp.Quit |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Excel from an ACCESS MODULE
Try replacing this:
With xlApp ...Workbooks.Open Filename:=StrPath ...Columns("K:L").Select With .Selection ...Interior.ColorIndex = 7 End With End With With xlApp with this: Set R = xlApp.Worksteets(1).Range("K1:L999") For Each C In R.Cells If C.Value = "NO" Then C.Interior.ColorIndex = 7 End If Next Set objR = Nothing A few things to note: 1) I assumed the Excel file you're working with only has one sheet. If it has more than one, your best bet is to change 'Worksteets(1)' with 'Worksheets("Sheet2")', using whatever the name of the sheet is 2) I also assumed that the area you'll need to check goes down to row 999. You may need to adjust that. If the size of the range you're going to search is extremely variable, there are ways you can dymatically change how far down the search goes. HTH -- Hmm...they have the Internet on COMPUTERS now! " wrote: I ahve tried posting this in the Access Module forum buit they told me to try here. Please note that I writing the VB code in an ACCESS Module not directly in EXCEL... Thanks in advance for your help! Here's a problem I have been stuck on for the last three months. I have a lot of VBA code in an access module that creates queries and tables. At the end of the code, I transfer all this information to an excel spreadsheet using the transfer spreadsheet option. I then have a batch of code which opens the spreadsheet, formats it, saves it and closes it. It works like a dream. However, the code is currently telling the excel spreadsheet to select collumns K and L and turn the whole columns pink. What I actually want is the code to search through columns K and L and turn an individual cell pink if the value is NO. Does this mean I have loop around each cell? How do I do this - i cant find any otherexamples on the net!! Any help or suggestions greatly appreciated. Here's the current code: Dim xlApp As Object Dim StrPath As String StrPath = "H:\Census_Checking\FSM_TO_BE_CHECKED.xls" Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True With xlApp ..Workbooks.Open Filename:=StrPath ..Columns("K:L").Select With .Selection ..Interior.ColorIndex = 7 End With End With With xlApp ..Cells.Select ..Cells.EntireColumn.AutoFit ..Rows("1:1").Select ..Selection.Font.Bold = True End With xlApp.Application.ActiveWorkbook.Save xlApp.Quit |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Excel from an ACCESS MODULE
SORRY! Slight bug in the code I gave you.
Set objWB = xlApp.Workbooks.Open(StrPath) Set R = objWB.Worksteets(1).Range("K1:L999") For Each C In R.Cells If C.Value = "NO" Then C.Interior.ColorIndex = 7 End If Next Set objR = Nothing Set objWB = Nothing -- Hmm...they have the Internet on COMPUTERS now! "MDW" wrote: Try replacing this: With xlApp ..Workbooks.Open Filename:=StrPath ..Columns("K:L").Select With .Selection ..Interior.ColorIndex = 7 End With End With With xlApp with this: Set R = xlApp.Worksteets(1).Range("K1:L999") For Each C In R.Cells If C.Value = "NO" Then C.Interior.ColorIndex = 7 End If Next Set objR = Nothing A few things to note: 1) I assumed the Excel file you're working with only has one sheet. If it has more than one, your best bet is to change 'Worksteets(1)' with 'Worksheets("Sheet2")', using whatever the name of the sheet is 2) I also assumed that the area you'll need to check goes down to row 999. You may need to adjust that. If the size of the range you're going to search is extremely variable, there are ways you can dymatically change how far down the search goes. HTH -- Hmm...they have the Internet on COMPUTERS now! " wrote: I ahve tried posting this in the Access Module forum buit they told me to try here. Please note that I writing the VB code in an ACCESS Module not directly in EXCEL... Thanks in advance for your help! Here's a problem I have been stuck on for the last three months. I have a lot of VBA code in an access module that creates queries and tables. At the end of the code, I transfer all this information to an excel spreadsheet using the transfer spreadsheet option. I then have a batch of code which opens the spreadsheet, formats it, saves it and closes it. It works like a dream. However, the code is currently telling the excel spreadsheet to select collumns K and L and turn the whole columns pink. What I actually want is the code to search through columns K and L and turn an individual cell pink if the value is NO. Does this mean I have loop around each cell? How do I do this - i cant find any otherexamples on the net!! Any help or suggestions greatly appreciated. Here's the current code: Dim xlApp As Object Dim StrPath As String StrPath = "H:\Census_Checking\FSM_TO_BE_CHECKED.xls" Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True With xlApp ..Workbooks.Open Filename:=StrPath ..Columns("K:L").Select With .Selection ..Interior.ColorIndex = 7 End With End With With xlApp ..Cells.Select ..Cells.EntireColumn.AutoFit ..Rows("1:1").Select ..Selection.Font.Bold = True End With xlApp.Application.ActiveWorkbook.Save xlApp.Quit |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Excel from an ACCESS MODULE
Sub PinkIt()
For Each c In Sheets(1).UsedRange.Cells If c.Value = "NO" Then c.Interior.ColorIndex = 26 End If Next End Sub -- urkec " wrote: I ahve tried posting this in the Access Module forum buit they told me to try here. Please note that I writing the VB code in an ACCESS Module not directly in EXCEL... Thanks in advance for your help! Here's a problem I have been stuck on for the last three months. I have a lot of VBA code in an access module that creates queries and tables. At the end of the code, I transfer all this information to an excel spreadsheet using the transfer spreadsheet option. I then have a batch of code which opens the spreadsheet, formats it, saves it and closes it. It works like a dream. However, the code is currently telling the excel spreadsheet to select collumns K and L and turn the whole columns pink. What I actually want is the code to search through columns K and L and turn an individual cell pink if the value is NO. Does this mean I have loop around each cell? How do I do this - i cant find any otherexamples on the net!! Any help or suggestions greatly appreciated. Here's the current code: Dim xlApp As Object Dim StrPath As String StrPath = "H:\Census_Checking\FSM_TO_BE_CHECKED.xls" Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True With xlApp ..Workbooks.Open Filename:=StrPath ..Columns("K:L").Select With .Selection ..Interior.ColorIndex = 7 End With End With With xlApp ..Cells.Select ..Cells.EntireColumn.AutoFit ..Rows("1:1").Select ..Selection.Font.Bold = True End With xlApp.Application.ActiveWorkbook.Save xlApp.Quit |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro in excel to call a access module to run. | Excel Discussion (Misc queries) | |||
Reference Class Module in Access from Excel | Excel Programming | |||
Running Excel module within Access | Excel Programming | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) | |||
how to call Access function&module in Excel VBA?? | Excel Programming |