Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 310
Default Excel 2007 - macro for formatting worksheet?

We are regularly importing CSV format data into an Excel worksheet and are
trying to find a method that users can easily apply specific formatting. The
data that is imported varies in numbers of rows and columns imported. Can
this be done with a macro, if yes, how?

If not, can anyone suggests another way of achieving this?

Thanks in anticipation
--
Michelle
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Excel 2007 - macro for formatting worksheet?

You have different number of columns so this seems like a general question.
Yo uare importing lots of different type data into workbooks. the best
method is probably to have a template workbook for each type data that you
are importing. then make a copy of the template and add your new data.

I very rarely start from a new blank workbook. I usually have similar data
in an old workbook which has the macros and formating that I need. then I
modify as required.

"Michelle" wrote:

We are regularly importing CSV format data into an Excel worksheet and are
trying to find a method that users can easily apply specific formatting. The
data that is imported varies in numbers of rows and columns imported. Can
this be done with a macro, if yes, how?

If not, can anyone suggests another way of achieving this?

Thanks in anticipation
--
Michelle

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Excel 2007 - macro for formatting worksheet?

Is the data always the same layout?

If yes...

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then I'm
about done. If the location or file name changes, I'll tweak the code to ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

Michelle wrote:

We are regularly importing CSV format data into an Excel worksheet and are
trying to find a method that users can easily apply specific formatting. The
data that is imported varies in numbers of rows and columns imported. Can
this be done with a macro, if yes, how?

If not, can anyone suggests another way of achieving this?

Thanks in anticipation
--
Michelle


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 310
Default Excel 2007 - macro for formatting worksheet?

Yes, the data is always in the same layout, except that there will be
variance in the number of columns and rows.

Your suggestion is just what I was looking for - I guess to cater for any
rows or columns I should format extra rows or columns when I record the
macro, and they can easily delete any extras?

Thanks very much for your help.
--
Michelle


"Dave Peterson" wrote:

Is the data always the same layout?

If yes...

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then I'm
about done. If the location or file name changes, I'll tweak the code to ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

Michelle wrote:

We are regularly importing CSV format data into an Excel worksheet and are
trying to find a method that users can easily apply specific formatting. The
data that is imported varies in numbers of rows and columns imported. Can
this be done with a macro, if yes, how?

If not, can anyone suggests another way of achieving this?

Thanks in anticipation
--
Michelle


--

Dave Peterson

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Excel 2007 - macro for formatting worksheet?

Or you could check to see what the last column used is.

Say you know you always have at least 5 columns (A:E), but only want to format
E:xx with a special number format.

You could determine the last column based on entries in row 1 (say). Then use
that in your code.

Dim LastCol as long
with activesheet 'after you've opened the csv file.
lastcol = .cells(1,.columns.count).end(xltoleft).column
.range("E1",.cells(1,lastcol)).entirecolumn.number format = "#,##0.00"
end with

Michelle wrote:

Yes, the data is always in the same layout, except that there will be
variance in the number of columns and rows.

Your suggestion is just what I was looking for - I guess to cater for any
rows or columns I should format extra rows or columns when I record the
macro, and they can easily delete any extras?

Thanks very much for your help.
--
Michelle

"Dave Peterson" wrote:

Is the data always the same layout?

If yes...

Start a new workbook

Start recording a macro that will be stored in this macro workbook.

File|Open your text file that needs to be imported. Format it the way you
like. Insert columns, add headers, freeze panes, widen columns, add filters, do
the page setup--everything you can think of.

When you're done, save that workbook with the macro.

I like to put a big button from the Forms toolbar on the only worksheet in that
macro workbook and assign this macro to that big button. I'll add a few
instructions to that sheet, too.

If the file name to open is always the same and in the same location, then I'm
about done. If the location or file name changes, I'll tweak the code to ask
for the file.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

Michelle wrote:

We are regularly importing CSV format data into an Excel worksheet and are
trying to find a method that users can easily apply specific formatting. The
data that is imported varies in numbers of rows and columns imported. Can
this be done with a macro, if yes, how?

If not, can anyone suggests another way of achieving this?

Thanks in anticipation
--
Michelle


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
save excel 2007 worksheet as pdf in macro Jania Excel Programming 2 September 29th 08 03:16 PM
Macro 2007 excel Conditional formatting column highlight Sue Excel Discussion (Misc queries) 0 April 18th 08 09:01 PM
Excel 2007 - cannot get worksheet change macro to work [email protected] Excel Programming 5 October 24th 07 04:43 PM
Worksheet formatting with Macro... Celt[_59_] Excel Programming 4 June 7th 06 06:54 AM
Help: VBA macro for worksheet formatting ylin Excel Programming 1 September 26th 04 06:30 AM


All times are GMT +1. The time now is 01:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"