Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
I've got a macro that takes a worksheet, does some formatting, then opens
another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
Best to set object variables and then always use these. Something along the
lines of Set oWBMain = Workbooks.Open(Filename:="C:\myData\myFile.csv") 'do your interim stuff Set oWBSecondary = Workbooks.Open(Filename:="C:\myData\otherFile.xls" ) and then always qualify with the wb object With oWBMain With .Worksheets(1) .Rang("A1").Value ="Head1") 'etc End With End With note the dots before the other objects which means they are properties of the previously declared object in the With statement. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "RSteph" wrote in message ... I've got a macro that takes a worksheet, does some formatting, then opens another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
It sounds like your code does not explicitly define which workbook and
worksheet to apply changes to- something like this: Range("A1").Interior.ColorIndex = 1 You have come up with the correct solution yourself! in VBA this would look something like this: Sub Demo Dim wsCSV as Worksheet, wsTarget as Worksheet 'set reference to the sheet to be formatted Set wsTarget = Activesheet 'do some formatting wsTarget.Range("A1").Interior.ColorIndex = 1 wsTarget.Range("A2").Font.Bold = True 'stop user seeing stuff Application.ScreenUpdating = False 'open the csv file, and create reference to first worksheet Set wsCSV = Worksheets.Open("C:\myfile.csv").Sheets(1) 'transfer some data from one sheet to the other wsTarget.Range("A1").Formula = wsCSV.Range("B2").Formula 'do some more formatting wsTarget.Range("A2").Interior.ColorIndex = 1 'close the CSV file when we are done wsCSV.Parent.Close savechanges:=False End Sub Cheers, Dave "RSteph" wrote: I've got a macro that takes a worksheet, does some formatting, then opens another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
If the Main Workbook is already open, will using the Workbook.Open command
cause me any problems? Or is there a way to specify the workbook that the macro was run from? (i.e. oWBMain = Workbook.Active 'type of command) "Bob Phillips" wrote: Best to set object variables and then always use these. Something along the lines of Set oWBMain = Workbooks.Open(Filename:="C:\myData\myFile.csv") 'do your interim stuff Set oWBSecondary = Workbooks.Open(Filename:="C:\myData\otherFile.xls" ) and then always qualify with the wb object With oWBMain With .Worksheets(1) .Rang("A1").Value ="Head1") 'etc End With End With note the dots before the other objects which means they are properties of the previously declared object in the With statement. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "RSteph" wrote in message ... I've got a macro that takes a worksheet, does some formatting, then opens another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
That worked perfectly. Thank you very much.
I even remembered to put in the Set command this time. "Dave Ramage" wrote: It sounds like your code does not explicitly define which workbook and worksheet to apply changes to- something like this: Range("A1").Interior.ColorIndex = 1 You have come up with the correct solution yourself! in VBA this would look something like this: Sub Demo Dim wsCSV as Worksheet, wsTarget as Worksheet 'set reference to the sheet to be formatted Set wsTarget = Activesheet 'do some formatting wsTarget.Range("A1").Interior.ColorIndex = 1 wsTarget.Range("A2").Font.Bold = True 'stop user seeing stuff Application.ScreenUpdating = False 'open the csv file, and create reference to first worksheet Set wsCSV = Worksheets.Open("C:\myfile.csv").Sheets(1) 'transfer some data from one sheet to the other wsTarget.Range("A1").Formula = wsCSV.Range("B2").Formula 'do some more formatting wsTarget.Range("A2").Interior.ColorIndex = 1 'close the CSV file when we are done wsCSV.Parent.Close savechanges:=False End Sub Cheers, Dave "RSteph" wrote: I've got a macro that takes a worksheet, does some formatting, then opens another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
set oWBMain = ActiveWorkbook
set oShMain = Activesheet -- Regards, Tom Ogilvy "RSteph" wrote: If the Main Workbook is already open, will using the Workbook.Open command cause me any problems? Or is there a way to specify the workbook that the macro was run from? (i.e. oWBMain = Workbook.Active 'type of command) "Bob Phillips" wrote: Best to set object variables and then always use these. Something along the lines of Set oWBMain = Workbooks.Open(Filename:="C:\myData\myFile.csv") 'do your interim stuff Set oWBSecondary = Workbooks.Open(Filename:="C:\myData\otherFile.xls" ) and then always qualify with the wb object With oWBMain With .Worksheets(1) .Rang("A1").Value ="Head1") 'etc End With End With note the dots before the other objects which means they are properties of the previously declared object in the With statement. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "RSteph" wrote in message ... I've got a macro that takes a worksheet, does some formatting, then opens another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro problems
I would test for it
On Error Resume Next Set oWBMain = Workbooks("myFile.csv") On Error Goto 0 If oWB Is Nothing Then Set oWBMain = Workbooks.Open(Filename:="C:\myData\myFile.csv") End If -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "RSteph" wrote in message ... If the Main Workbook is already open, will using the Workbook.Open command cause me any problems? Or is there a way to specify the workbook that the macro was run from? (i.e. oWBMain = Workbook.Active 'type of command) "Bob Phillips" wrote: Best to set object variables and then always use these. Something along the lines of Set oWBMain = Workbooks.Open(Filename:="C:\myData\myFile.csv") 'do your interim stuff Set oWBSecondary = Workbooks.Open(Filename:="C:\myData\otherFile.xls" ) and then always qualify with the wb object With oWBMain With .Worksheets(1) .Rang("A1").Value ="Head1") 'etc End With End With note the dots before the other objects which means they are properties of the previously declared object in the With statement. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "RSteph" wrote in message ... I've got a macro that takes a worksheet, does some formatting, then opens another workbook, and is suppose to pull information from that workbook, into the original file. The original file is a .csv file made from another program, that I open in excel with comma's as the deliminater, to populate the first 3 columns. So the name won't always be the same. The problem I'm having is that when I run the macro from my "personal workbook" it starts the formatting, but when it goes to opening the new file to get some data, it starts doing the rest of the formatting in that file. How can I go about getting it to do the formatting in the first file? Is there a way for me to, when I start the macro, declare the page it was started from into a variable, so I can then use that to specify the page I want it to work from. Also, is there a way to hide the second file once I open it, and still read data from it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Problems | Excel Programming | |||
Macro Problems | Excel Programming | |||
various macro problems | Excel Programming | |||
Macro problems in XP | Excel Programming | |||
macro problems | Excel Programming |