Visual Basic 4 / 5 / 6 RSS Visual Basic 4 / 5 / 6 RSS

Saving excel sheet with few column names present as file name

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site
Thread Solved

•
•
Join Date: Apr 2007
Posts: 28
Reputation: Shilpa Jain is an unknown quantity at this point 
Solved Threads: 0
Shilpa Jain Shilpa Jain is offline Offline
Light Poster

Saving excel sheet with few column names present as file name

 
0
  #1
Jul 11th, 2008
Hi All,

I am creating and saving the excel sheet at runtime(it is a copy of report generated in flexgrid). I need to add some of the column names in the excel sheet name while saving. e.g. Excel sheet should be saved as Report_Nid_Status_RBAC_Status_Today'sDate.

Nid_Status and RBAC_Status are column names in the sheet. Columns can vary and so the sheet name according to the columns. How I can save an excel sheet like this?

Regards,
Shilpa
Last edited by Shilpa Jain; Jul 11th, 2008 at 1:19 am.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2007
Posts: 28
Reputation: Shilpa Jain is an unknown quantity at this point 
Solved Threads: 0
Shilpa Jain Shilpa Jain is offline Offline
Light Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #2
Jul 15th, 2008
Please help me in sorting out the above mentioned problem. It is giving me nightmares. I have saved the file like Report_Today'sDate but adding variable column names is creating problems for me.

Regards,
Shilpa
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 14
Reputation: P90K is an unknown quantity at this point 
Solved Threads: 1
P90K's Avatar
P90K P90K is offline Offline
Newbie Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #3
Jul 15th, 2008
To save the excel workbook with vb you need to use next line:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. xl.activeworkbook.saveas "C:\... " & var1 & "text" & var2 & var3 ... ".xls"

I hope this was helpfull for you.

gr
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2007
Posts: 28
Reputation: Shilpa Jain is an unknown quantity at this point 
Solved Threads: 0
Shilpa Jain Shilpa Jain is offline Offline
Light Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #4
Jul 15th, 2008
Thanks for replying. But , I am unable to get what you are trying to say. As per my understanding, this can be used only when the names are fixed. Correct me if I am wrong.

I have 3 columns in my report viz. NID_Status,ISD_Status,RBAC_status. Report can containg any of these 3 fields or a combination of them. For example, It can be Report_NID_Status_ISD_Status_TodaysDate if it contains only NID and Rbac columns or it can be Report_NID_Status_TodaysDate if only NID column is present or Report_NID_Status_ISD_Status_RBCA_Status_TodaysDate if all 3 columns are present in the report. The report name will vary depending which all columns are present in it. How do I incorporate these variable names in the report.

As of now, I am saving the file in Report_Date format as:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. ApExcel.ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\All Users\Desktop\Report_" & _
  2. Format(Now(), "dd-mm-yyyy_hhmm") & ".xls", _
  3. CreateBackup:=False

Please help.

Regards,
Shilpa
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 14
Reputation: P90K is an unknown quantity at this point 
Solved Threads: 1
P90K's Avatar
P90K P90K is offline Offline
Newbie Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #5
Jul 15th, 2008
I don't think I completly understand you question.
you want to know how you read the name from the cell (in an excel file) and then dicide if the name need to be in the title.

Is that you question ??
or am I wrong??

gr

PS I'm sorry for my englisch.
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2007
Posts: 28
Reputation: Shilpa Jain is an unknown quantity at this point 
Solved Threads: 0
Shilpa Jain Shilpa Jain is offline Offline
Light Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #6
Jul 16th, 2008
You have almost understand my question. I have atleast 5 columns in my report at a time. One of them would be like NID_Status. I only need to insert the *_Status column in my report name. Others should not be there.

I am attaching few samples of the report. It is named in the format of Report_Date_Time. It should be Report_*_Status_*_Status_Date_Time.The columns containing _Status only have to be present in the report name.

I hope you have understood my problem and will help me.

Regards,
Shilpa
Attached Files
File Type: zip Sample Reports.zip (21.1 KB, 5 views)
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 14
Reputation: P90K is an unknown quantity at this point 
Solved Threads: 1
P90K's Avatar
P90K P90K is offline Offline
Newbie Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #7
Jul 16th, 2008
K. I think I understand your problem.
I can not read you attachment for some reason. did you make that in excel???
because when I opend it with excel I get an error that the file was not created with excel.

I will try to explain

I asume your collum titels are in row 1 collum 1 to 5 ???

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. Dim var1, var2, var3, var4, var5
  2. Dim ApExcel As Excel.Application 
  3. Set ApExcel = New Excel.Application

then you open your workbook or make a new and fil it in.
then, when you want to save the file, you use next code.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
  1. Windows("name of your excel sheet.xls").Activate    'some times nessesary
  2. if cells(1,1) <> "" then
  3.           var1 = cells(1,1) & "_Status_"
  4. end if
  5. if cells(1,2) <> "" then
  6.            var2=cells(1,2) & "_Status_"
  7. end if
  8. if cells(1,3) <> "" then
  9.            var3=cells(1,3) & "_Status_"
  10. end if
  11. if cells(1,4) <> "" then
  12.            var4=cells(1,4) & "_Status_"
  13. end if
  14. if cells(1,5) <> "" then
  15.            var5=cells(1,5) & "_Status_"
  16. end if
  17.  
  18. ApExcel.ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\All Users\Desktop\Report_" & var1 & var2 & var3 & var4 & var5 &
  19. Format(Now(), "dd-mm-yyyy_hhmm") & ".xls"

I hope this solve you problem or was a little bit use full to solve you problem.

gr
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2007
Posts: 28
Reputation: Shilpa Jain is an unknown quantity at this point 
Solved Threads: 0
Shilpa Jain Shilpa Jain is offline Offline
Light Poster

Re: Saving excel sheet with few column names present as file name

 
0
  #8
Jul 18th, 2008
Thanks P90K. My problem is resolved. I used a loop to run it

Regards,
Shilpa
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Visual Basic 4 / 5 / 6 Forum
Thread Tools Search this Thread



* 6 429 2007 access activex add age append application basic beginner birth bmp calculator cd cells.find click client code college column component connection connectionproblemusingvb6usingoledb copy creat ctrl+f data database datareport date delete dissertations dissertationthesis dissertationtopic edit error excel excelmacro file filename form hardware header iamthwee image inboxinvb internetfiledownload keypress label listbox listview liveperson login looping machine microsoft movingranges number objectinsert open oracle password prime program prompt range-objects readfile reading record refresh remotesqlserverdatabase report retrieve save search sendbyte sites sort sql sql2008 sqlserver subroutine table tags textbox time urldownloadtofile vb vb6 vb6.0 vba visual visualbasic visualbasic6 web window windows
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC