Excel & MS Query caused compile errors

[Simon Shaw]

Hi,

After connecting to BusinessVision tables and returning data to Excel, my
VBA code in excel suddenly became un-compileable. Simple code syntax such as
declaring a variable was suddenly invalid. I would comment out the offending
lines to see what else failed, and stopped after about the 12th code failure.

The code compiles fine in numerous environments and runs in Excel 2000, 2003
& 2007 against Pervasive, MS Access, SQL Server, My SQL and Oracle databases.

The error occurs just after manually querying BusinessVision 7.3 with
Pervasive 10 database with Excel for the first time.

Fix Attempts include:
- Uninstalling/re-installing Excel
- removing and adding the System DSN files in the ODBC Drivers
- uninstalling BusinessVision and pervasive
- applying all updates to Excel, BusinessVision and the OS.
none worked

I then started with a clean windows server, installed excel, then installed
BusinessVision. the VBA code still compiled. I then added the System DSN
file. the VBA code still compiled fine. I then performed a manual database
query to retrieve data from the AR - Transaction tables, which was
successful. I then tried to compile the VBA code and it failed - miserably...


thoughts?

Thank you!

 

[Joel]

You didn't specify what happened to cause the problem. did you perform any
updates to windows, Office, or other products that may of caused the cahnge.
Do yo uhave your window updates set in automatic update mode or did your MIS
department push some updates on your PC?

I suspect one of the DLL got updated.check the References in the VBA menu
tools - References and see if any of the select libraries got updated.

 

[Greenwind]

I'm sorry for hijacking this question but somehow I just cannot post any new
question here.

My data is in an Excel file and I'd like to fill a column within this file.
After connecting this file to MS Query, I’d like to compare Column A and D
then use the content of Cloumn B to fill column F.

There are 3 groups in my data. Group 1: Column A & Column B.
Group 2: Column C & D. Group 3: Column E and F. Column A has monthly data
from Jan 1988 to Jan 2006. Notice although 31 Jan 88 is a date, it means a
period from Jan 01, 1988 to Jan 31, 1988. Both dates, Jan 01, 1988 and Jan
31, 1988 are included. Column B is the debt of the corresponding month. e.g.
From Jan 01, 1988 to Jan 31, 1988, the debt is 2.3343. Column C and D are the
companies’ names and debt issuing dates respectively. In column C, there are
848 different firms. However, the starting and end dates are different in
these two columns. That is, according to column D, company A0004 does not
have any issuing dates during 1988-2000. The issuing dates for A0004 only
exist
during 2001 and 2005. There is another example: A0005’s issuing dates are
from 1989 to 2005. Column E has 848 firms although firms’ names are not
listed in column E. Each firm has a fixed format which starts with 1988 and
finishes with 2005 in column E.

Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 31/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 March 4.5711 A0004 30/09/2003 …
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A005 31/12/1989 …
31 Dec 89 3.0482 A005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … … … …
… … … … 2005
… … … … 1988
… … … … …
.... ... A005 31/12/2005 2001
… … …

… … 2005
… … …
In order to compare Column A and D, I have to change them to the same
format. Effectively if the the month in Column A and D are the same, I can
put the content of Column B into Column F. For instance, because 31/12/2001
corresponds to 31 Dec 01 in Column A, 3.1124 should be placed after the 2001
in column E for firm A0004.

As for the 1st and the 2nd cell in column F, the year-end-values in column B
are used. Based on column D, there is no issuing date for A0004 during
1988-2000. Therefore, we assume the issuing date for A0004 in 1998 is the
last date of 1988, i.e. 31 Dec 88. Hence 3.0045 is put in the 1st cell in
column F. Similarly, 3.0482 is placed after 1989 in column E.

I tried the first step: SELECT Time
UPDATE Time
SET Time = 31/01/1988
WHERE Time = 31 Jan 88
The error message is: Syntax error in query expression 'Time UPDATE Time SET
Time = 31/01/1988 WHERE Time = 31 Jan 88'. Why?

Thank you so much!

 

[Joel]

I have lots of quetions but lets take it slow at first.

Your error message is being cause because you are trying to modify a
worksheet that is linked by a query to a database. You need to copy the data
to a new sheet to be able to modify the data. The code below will
automaticaly copy the data to an existing worksheet

Sub CopySheet()

Set Sourcesht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

Sourcesht.Cells.Copy Destination:=DestSht.Cells

End Sub


1) My problem with your posting is I'm not sure is you need to query the
database to get additional data or you are just formating the data that
already exists on the worksheet. Instead of qerying the database you can
actually open the database and retrieve the data one row at a time.

2) I also need futher explaination with your statment of dates "I have to
change them to the same format". You don't mean formating the cells on the
worksheet but make the dates the same time reference that same such as the
last date of the month.

3) I think you should start by filling in the missing dates in columns A - D
to make it easier to perform lookups. You can do this automatically with
code.

I think you want to attempt to do most of the work yourself with me advising?

 

[Greenwind]

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?

Thank you so much for your time and input!

 

[Joel]

See below

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

-------------------------------------------------------------------------------------------
You don't need a server for SQL. the SQL is really the CommandText portion
of the query. Dont learn SQL let Excel generate it for you. Start recording
a macro from the worksheet menu - tools - Macro _record New Macro. Then
perform a query by going to worksheet menu Data - Import External Data.
Choose any of the query methods.


-------------------------------------------------------------------------------------------

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

You can compare just the month and year

ColA_Date = Range("A2")
ColD_Date = Range("D2")

if month(ColA_Date) = month(ColD_Date) and _
year(ColA_Date) = year(ColD_Date) then

You can also force the date to the 1st of the month

ColA_Date = Range("A2")
FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1)

You can also force the date to the last day of the month by going to 1st of
next month and subtracting 1 and it even works if the date is in December.
VBA thinks there are 13 months

MyDate = "12/13/09"
LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1

3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?

I would write the entire code for you but your wouldn't learn as much as
doing it yourself. I will help.

 

[Greenwind]

Thank you so much for the advice. There is a small problem regarding changing
the format of dates in Column A though. I only can search the date 31 Jan 88
but I cannot change it within MS query. Yes, in my last post, I thought I
can change the dates. "It is feasible to open the database and retrieve the
data one row at a timeâ€. Do you know how? Thanks!

 

[Joel]

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

 

[Greenwind]

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
..style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>

 

[Joel]

Wrongftype macro

From excel 2003 menu
Tools - Macro - Start Recording

or 2007
View Macros - Start Recording

The macro can be found by type Alt F11 to get to VBA and clicking on Module1
in the VBA project window

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
--
Tatiana

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

 

[Greenwind]

I'm using Excel 2003. I followed the method (Tools - Macro - Start Recording)
to record the Macro. This Macro is called Macro1. If you're talking about the
VBA macro. Here we go:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2009-6-6 by 0710058c
'

'
End Sub

Since I don't think this 'Macro1' showed much, I posted the Macro in
Microsoft Script Editor last time. Many thanks!

Wrongftype macro

From excel 2003 menu
Tools - Macro - Start Recording

or 2007
View Macros - Start Recording

The macro can be found by type Alt F11 to get to VBA and clicking on Module1
in the VBA project window

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
--
Tatiana

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

:

Thank you so much for the advice. There is a small problem regarding changing
the format of dates in Column A though. I only can search the date 31 Jan 88
but I cannot change it within MS query. Yes, in my last post, I thought I
can change the dates. "It is feasible to open the database and retrieve the
data one row at a timeâ€. Do you know how? Thanks!
--
Tatiana


:

See below

:

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

-------------------------------------------------------------------------------------------
You don't need a server for SQL. the SQL is really the CommandText portion
of the query. Dont learn SQL let Excel generate it for you. Start recording
a macro from the worksheet menu - tools - Macro _record New Macro. Then
perform a query by going to worksheet menu Data - Import External Data.
Choose any of the query methods.


-------------------------------------------------------------------------------------------

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

You can compare just the month and year

ColA_Date = Range("A2")
ColD_Date = Range("D2")

if month(ColA_Date) = month(ColD_Date) and _
year(ColA_Date) = year(ColD_Date) then

You can also force the date to the 1st of the month

ColA_Date = Range("A2")
FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1)

You can also force the date to the last day of the month by going to 1st of
next month and subtracting 1 and it even works if the date is in December.
VBA thinks there are 13 months

MyDate = "12/13/09"
LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1






3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?

I would write the entire code for you but your wouldn't learn as much as
doing it yourself. I will help.

Thank you so much for your time and input!

--
Tatiana


:

I have lots of quetions but lets take it slow at first.

Your error message is being cause because you are trying to modify a
worksheet that is linked by a query to a database. You need to copy the data
to a new sheet to be able to modify the data. The code below will
automaticaly copy the data to an existing worksheet

Sub CopySheet()

Set Sourcesht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

Sourcesht.Cells.Copy Destination:=DestSht.Cells

End Sub


1) My problem with your posting is I'm not sure is you need to query the
database to get additional data or you are just formating the data that
already exists on the worksheet. Instead of qerying the database you can
actually open the database and retrieve the data one row at a time.

2) I also need futher explaination with your statment of dates "I have to
change them to the same format". You don't mean formating the cells on the
worksheet but make the dates the same time reference that same such as the

 

[Joel]

Did you return any data when you performed the query? You should of return
some data if you went to the Data Import External data an setup yoiu query
properly. Ive done this before with webqueries and data bases I don't have
access to a SQL server.

What you may want to due to understand the syntax better is to close the
Excel Workbook you are woring with. The open a new Excel workbook. Start
recording a macro and go to menu Data - Import External Data - New Query From
Database. Then select Excel file and the workbook you closed. You will see
the proper snytax for the dat you are looking for.

The conection property of the query can be from a file or a server. The
syntax is diferent but the SQL (command text is the same). The Table in
Access Database is the worksheet in excel. Row 1 of the Excel worksheet is
the

I'm using Excel 2003. I followed the method (Tools - Macro - Start Recording)
to record the Macro. This Macro is called Macro1. If you're talking about the
VBA macro. Here we go:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2009-6-6 by 0710058c
'

'
End Sub

Since I don't think this 'Macro1' showed much, I posted the Macro in
Microsoft Script Editor last time. Many thanks!

Wrongftype macro

From excel 2003 menu
Tools - Macro - Start Recording

or 2007
View Macros - Start Recording

The macro can be found by type Alt F11 to get to VBA and clicking on Module1
in the VBA project window

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
--
Tatiana


:

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

:

Thank you so much for the advice. There is a small problem regarding changing
the format of dates in Column A though. I only can search the date 31 Jan 88
but I cannot change it within MS query. Yes, in my last post, I thought I
can change the dates. "It is feasible to open the database and retrieve the
data one row at a timeâ€. Do you know how? Thanks!
--
Tatiana


:

See below

:

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

-------------------------------------------------------------------------------------------
You don't need a server for SQL. the SQL is really the CommandText portion
of the query. Dont learn SQL let Excel generate it for you. Start recording
a macro from the worksheet menu - tools - Macro _record New Macro. Then
perform a query by going to worksheet menu Data - Import External Data.
Choose any of the query methods.


-------------------------------------------------------------------------------------------

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

You can compare just the month and year

ColA_Date = Range("A2")
ColD_Date = Range("D2")

if month(ColA_Date) = month(ColD_Date) and _
year(ColA_Date) = year(ColD_Date) then

You can also force the date to the 1st of the month

ColA_Date = Range("A2")
FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1)

You can also force the date to the last day of the month by going to 1st of
next month and subtracting 1 and it even works if the date is in December.
VBA thinks there are 13 months

MyDate = "12/13/09"
LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1






3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?

I would write the entire code for you but your wouldn't learn as much as
doing it yourself. I will help.

Thank you so much for your time and input!

--
Tatiana


:

I have lots of quetions but lets take it slow at first.

Your error message is being cause because you are trying to modify a
worksheet that is linked by a query to a database. You need to copy the data
to a new sheet to be able to modify the data. The code below will
automaticaly copy the data to an existing worksheet

Sub CopySheet()

 

[Greenwind]

After tried 10 times of what you told me below, I'm afraid that I still
cannot change any item within the query. The data is returned after I set the
criteria straight away. Apart the syntax is the same for the VBA one.

Did you return any data when you performed the query? You should of return
some data if you went to the Data Import External data an setup yoiu query
properly. Ive done this before with webqueries and data bases I don't have
access to a SQL server.

What you may want to due to understand the syntax better is to close the
Excel Workbook you are woring with. The open a new Excel workbook. Start
recording a macro and go to menu Data - Import External Data - New Query From
Database. Then select Excel file and the workbook you closed. You will see
the proper snytax for the dat you are looking for.

The conection property of the query can be from a file or a server. The
syntax is diferent but the SQL (command text is the same). The Table in
Access Database is the worksheet in excel. Row 1 of the Excel worksheet is
the

I'm using Excel 2003. I followed the method (Tools - Macro - Start Recording)
to record the Macro. This Macro is called Macro1. If you're talking about the
VBA macro. Here we go:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2009-6-6 by 0710058c
'

'
End Sub

Since I don't think this 'Macro1' showed much, I posted the Macro in
Microsoft Script Editor last time. Many thanks!

Wrongftype macro

From excel 2003 menu
Tools - Macro - Start Recording

or 2007
View Macros - Start Recording

The macro can be found by type Alt F11 to get to VBA and clicking on Module1
in the VBA project window



:

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
--
Tatiana


:

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

:

Thank you so much for the advice. There is a small problem regarding changing
the format of dates in Column A though. I only can search the date 31 Jan 88
but I cannot change it within MS query. Yes, in my last post, I thought I
can change the dates. "It is feasible to open the database and retrieve the
data one row at a timeâ€. Do you know how? Thanks!
--
Tatiana


:

See below

:

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

-------------------------------------------------------------------------------------------
You don't need a server for SQL. the SQL is really the CommandText portion
of the query. Dont learn SQL let Excel generate it for you. Start recording
a macro from the worksheet menu - tools - Macro _record New Macro. Then
perform a query by going to worksheet menu Data - Import External Data.
Choose any of the query methods.


-------------------------------------------------------------------------------------------

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

You can compare just the month and year

ColA_Date = Range("A2")
ColD_Date = Range("D2")

if month(ColA_Date) = month(ColD_Date) and _
year(ColA_Date) = year(ColD_Date) then

You can also force the date to the 1st of the month

ColA_Date = Range("A2")
FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1)

You can also force the date to the last day of the month by going to 1st of
next month and subtracting 1 and it even works if the date is in December.
VBA thinks there are 13 months

MyDate = "12/13/09"
LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1






3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?

 

[Greenwind]

As for the Microsoft Script Editior, there are 2 Macros. One is for 'Book1'
as below:
<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="Book1_files/filelist.xml">
<link rel=Edit-Time-Data href="Book1_files/editdata.mso">
<link rel=OLE-Object-Data href="Book1_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-07T14:19:55Z</o:Created>
<o:LastSaved>2009-06-07T14:33:54Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]--><![if !supportTabStrip]>
<link id="shLink" href="Book1_files/sheet001.htm">
<link id="shLink" href="Book1_files/sheet002.htm">
<link id="shLink" href="Book1_files/sheet003.htm">

<link id="shLink">

<script language="JavaScript">
<!--
var c_lTabs=3;

var c_rgszSh=new Array(c_lTabs);
c_rgszSh[0] = "Sheet1";
c_rgszSh[1] = "Sheet2";
c_rgszSh[2] = "Sheet3";

var c_rgszClr=new Array(8);
c_rgszClr[0]="window";
c_rgszClr[1]="buttonface";
c_rgszClr[2]="windowframe";
c_rgszClr[3]="windowtext";
c_rgszClr[4]="threedlightshadow";
c_rgszClr[5]="threedhighlight";
c_rgszClr[6]="threeddarkshadow";
c_rgszClr[7]="threedshadow";

var g_iShCur;
var g_rglTabX=new Array(c_lTabs);

function fnGetIEVer()
{
var ua=window.navigator.userAgent
var msie=ua.indexOf("MSIE")
if (msie>0 && window.navigator.platform=="Win32")
return parseInt(ua.substring(msie+5,ua.indexOf(".", msie)));
else
return 0;
}

function fnBuildFrameset()
{
var szHTML="<frameset rows=\"*,18\" border=0 width=0 frameborder=no
framespacing=0>"+
"<frame src=\""+document.all.item("shLink")[0].href+"\" name=\"frSheet\"
noresize>"+
"<frameset cols=\"54,*\" border=0 width=0 frameborder=no framespacing=0>"+
"<frame src=\"\" name=\"frScroll\" marginwidth=0 marginheight=0
scrolling=no>"+
"<frame src=\"\" name=\"frTabs\" marginwidth=0 marginheight=0
scrolling=no>"+
"</frameset></frameset><plaintext>";

with (document) {
open("text/html","replace");
write(szHTML);
close();
}

fnBuildTabStrip();
}

function fnBuildTabStrip()
{
var szHTML=
"<html><head><style>.clScroll {font:8pt Courier
New;color:"+c_rgszClr[6]+";cursor:default;line-height:10pt;}"+
".clScroll2 {font:10pt
Arial;color:"+c_rgszClr[6]+";cursor:default;line-height:11pt;}</style></head>"+
"<body onclick=\"event.returnValue=false;\"
ondragstart=\"event.returnValue=false;\"
onselectstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+"
topmargin=0 leftmargin=0><table cellpadding=0 cellspacing=0 width=100%>"+
"<tr><td colspan=6 height=1 bgcolor="+c_rgszClr[2]+"></td></tr>"+
"<tr><td style=\"font:1pt\"> <td>"+
"<td valign=top id=tdScroll class=\"clScroll\"
onclick=\"parent.fnFastScrollTabs(0);\"
onmouseover=\"parent.fnMouseOverScroll(0);\"
onmouseout=\"parent.fnMouseOutScroll(0);\"><a>«</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\"
onclick=\"parent.fnScrollTabs(0);\" ondblclick=\"parent.fnScrollTabs(0);\"
onmouseover=\"parent.fnMouseOverScroll(1);\"
onmouseout=\"parent.fnMouseOutScroll(1);\"><a>&lt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\"
onclick=\"parent.fnScrollTabs(1);\" ondblclick=\"parent.fnScrollTabs(1);\"
onmouseover=\"parent.fnMouseOverScroll(2);\"
onmouseout=\"parent.fnMouseOutScroll(2);\"><a>&gt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll\"
onclick=\"parent.fnFastScrollTabs(1);\"
onmouseover=\"parent.fnMouseOverScroll(3);\"
onmouseout=\"parent.fnMouseOutScroll(3);\"><a>»</a></td>"+
"<td style=\"font:1pt\"> <td></tr></table></body></html>";

with (frames['frScroll'].document) {
open("text/html","replace");
write(szHTML);
close();
}

szHTML =
"<html><head>"+
"<style>A:link,A:visited,A:active
{text-decoration:none;"+"color:"+c_rgszClr[3]+";}"+
".clTab {cursor:hand;background:"+c_rgszClr[1]+";font:9pt
Arial;padding-left:3px;padding-right:3px;text-align:center;}"+
".clBorder {background:"+c_rgszClr[2]+";font:1pt;}"+
"</style></head><body onload=\"parent.fnInit();\"
onselectstart=\"event.returnValue=false;\"
ondragstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+
" topmargin=0 leftmargin=0><table id=tbTabs cellpadding=0 cellspacing=0>";

var iCellCount=(c_lTabs+1)*2;

var i;
for (i=0;i<iCellCount;i+=2)
szHTML+="<col width=1><col>";

var iRow;
for (iRow=0;iRow<6;iRow++) {

szHTML+="<tr>";

if (iRow==5)
szHTML+="<td colspan="+iCellCount+"></td>";
else {
if (iRow==0) {
for(i=0;i<iCellCount;i++)
szHTML+="<td height=1 class=\"clBorder\"></td>";
} else if (iRow==1) {
for(i=0;i<c_lTabs;i++) {
szHTML+="<td height=1 nowrap class=\"clBorder\"> </td>";
szHTML+=
"<td id=tdTab height=1 nowrap class=\"clTab\"
onmouseover=\"parent.fnMouseOverTab("+i+");\"
onmouseout=\"parent.fnMouseOutTab("+i+");\">"+
"<a href=\""+document.all.item("shLink").href+"\"
target=\"frSheet\" id=aTab> "+c_rgszSh+" </a></td>";
}
szHTML+="<td id=tdTab height=1 nowrap class=\"clBorder\"><a
id=aTab> </a></td><td width=100%></td>";
} else if (iRow==2) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1></td><td height=1 class=\"clBorder\"></td>";
szHTML+="<td height=1></td><td height=1></td>";
} else if (iRow==3) {
for (i=0;i<iCellCount;i++)
szHTML+="<td height=1></td>";
} else if (iRow==4) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1 width=1></td><td height=1></td>";
szHTML+="<td height=1 width=1></td><td></td>";
}
}
szHTML+="</tr>";
}

szHTML+="</table></body></html>";
with (frames['frTabs'].document) {
open("text/html","replace");
charset=document.charset;
write(szHTML);
close();
}
}

function fnInit()
{
g_rglTabX[0]=0;
var i;
for (i=1;i<=c_lTabs;i++)
with (frames['frTabs'].document.all.tbTabs.rows[1].cells[fnTabToCol(i-1)])
g_rglTabX=offsetLeft+offsetWidth-6;
}

function fnTabToCol(iTab)
{
return 2*iTab+1;
}

function fnNextTab(fDir)
{
var iNextTab=-1;
var i;

with (frames['frTabs'].document.body) {
if (fDir==0) {
if (scrollLeft>0) {
for (i=0;i<c_lTabs&&g_rglTabX<scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i-1;
}
} else {
if (g_rglTabX[c_lTabs]+6>offsetWidth+scrollLeft) {
for (i=0;i<c_lTabs&&g_rglTabX<=scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i;
}
}
}
return iNextTab;
}

function fnScrollTabs(fDir)
{
var iNextTab=fnNextTab(fDir);

if (iNextTab>=0) {
frames['frTabs'].scroll(g_rglTabX[iNextTab],0);
return true;
} else
return false;
}

function fnFastScrollTabs(fDir)
{
if (c_lTabs>16)
frames['frTabs'].scroll(g_rglTabX[fDir?c_lTabs-1:0],0);
else
if (fnScrollTabs(fDir)>0)
window.setTimeout("fnFastScrollTabs("+fDir+");",5);
}

function fnSetTabProps(iTab,fActive)
{
var iCol=fnTabToCol(iTab);
var i;

if (iTab>=0) {
with (frames['frTabs'].document.all) {
with (tbTabs) {
for (i=0;i<=4;i++) {
with (rows) {
if (i==0)
cells[iCol].style.background=c_rgszClr[fActive?0:2];
else if (i>0 && i<4) {
if (fActive) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[0];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
if (i==1) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[1];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
cells[iCol-1].style.background=c_rgszClr[4];
cells[iCol].style.background=c_rgszClr[(i==2)?2:4];
cells[iCol+1].style.background=c_rgszClr[4];
}
}
} else
cells[iCol].style.background=c_rgszClr[fActive?2:4];
}
}
}
with (aTab[iTab].style) {
cursor=(fActive?"default":"hand");
color=c_rgszClr[3];
}
}
}
}

function fnMouseOverScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[7];
}

function fnMouseOutScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[6];
}

function fnMouseOverTab(iTab)
{
if (iTab!=g_iShCur) {
var iCol=fnTabToCol(iTab);
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[5];
}
}
}

function fnMouseOutTab(iTab)
{
if (iTab>=0) {
var elFrom=frames['frTabs'].event.srcElement;
var elTo=frames['frTabs'].event.toElement;

if ((!elTo) ||
(elFrom.tagName==elTo.tagName) ||
(elTo.tagName=="A" && elTo.parentElement!=elFrom) ||
(elFrom.tagName=="A" && elFrom.parentElement!=elTo)) {

if (iTab!=g_iShCur) {
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[1];
}
}
}
}
}

function fnSetActiveSheet(iSh)
{
if (iSh!=g_iShCur) {
fnSetTabProps(g_iShCur,false);
fnSetTabProps(iSh,true);
g_iShCur=iSh;
}
}

window.g_iIEVer=fnGetIEVer();
if (window.g_iIEVer>=4)
fnBuildFrameset();
//-->
</script>
<![endif]><!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetSource HRef="Book1_files/sheet001.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet2</x:Name>
<x:WorksheetSource HRef="Book1_files/sheet002.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet3</x:Name>
<x:WorksheetSource HRef="Book1_files/sheet003.htm"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:Stylesheet HRef="Book1_files/stylesheet.css"/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>10395</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<frameset rows="*,39" border=0 width=0 frameborder=no framespacing=0>
<frame src="Book1_files/sheet001.htm" name="frSheet">
<frame src="Book1_files/tabstrip.htm" name="frTabs" marginwidth=0
marginheight=0>
<noframes>
<body>
<p>This page uses frames, but your browser doesn't support them.</p>
</body>
</noframes>
</frameset>
</html>

Did you return any data when you performed the query? You should of return
some data if you went to the Data Import External data an setup yoiu query
properly. Ive done this before with webqueries and data bases I don't have
access to a SQL server.

What you may want to due to understand the syntax better is to close the
Excel Workbook you are woring with. The open a new Excel workbook. Start
recording a macro and go to menu Data - Import External Data - New Query From
Database. Then select Excel file and the workbook you closed. You will see
the proper snytax for the dat you are looking for.

The conection property of the query can be from a file or a server. The
syntax is diferent but the SQL (command text is the same). The Table in
Access Database is the worksheet in excel. Row 1 of the Excel worksheet is
the

I'm using Excel 2003. I followed the method (Tools - Macro - Start Recording)
to record the Macro. This Macro is called Macro1. If you're talking about the
VBA macro. Here we go:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2009-6-6 by 0710058c
'

'
End Sub

Since I don't think this 'Macro1' showed much, I posted the Macro in
Microsoft Script Editor last time. Many thanks!

Wrongftype macro

From excel 2003 menu
Tools - Macro - Start Recording

or 2007
View Macros - Start Recording

The macro can be found by type Alt F11 to get to VBA and clicking on Module1
in the VBA project window



:

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
--
Tatiana


:

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

:

Thank you so much for the advice. There is a small problem regarding changing
the format of dates in Column A though. I only can search the date 31 Jan 88
but I cannot change it within MS query. Yes, in my last post, I thought I
can change the dates. "It is feasible to open the database and retrieve the
data one row at a timeâ€. Do you know how? Thanks!
--
Tatiana


:

See below

:

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

-------------------------------------------------------------------------------------------
You don't need a server for SQL. the SQL is really the CommandText portion
of the query. Dont learn SQL let Excel generate it for you. Start recording
a macro from the worksheet menu - tools - Macro _record New Macro. Then
perform a query by going to worksheet menu Data - Import External Data.
Choose any of the query methods.


-------------------------------------------------------------------------------------------

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

You can compare just the month and year

ColA_Date = Range("A2")
ColD_Date = Range("D2")

if month(ColA_Date) = month(ColD_Date) and _
year(ColA_Date) = year(ColD_Date) then

You can also force the date to the 1st of the month

ColA_Date = Range("A2")
FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1)

You can also force the date to the last day of the month by going to 1st of
next month and subtracting 1 and it even works if the date is in December.
VBA thinks there are 13 months

MyDate = "12/13/09"
LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1






3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?

 

[Greenwind]

The second one is for the personal workbook:
<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="Book1_files/filelist.xml">
<link rel=Edit-Time-Data href="Book1_files/editdata.mso">
<link rel=OLE-Object-Data href="Book1_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-07T14:19:55Z</o:Created>
<o:LastSaved>2009-06-07T14:33:54Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]--><![if !supportTabStrip]>
<link id="shLink" href="Book1_files/sheet001.htm">
<link id="shLink" href="Book1_files/sheet002.htm">
<link id="shLink" href="Book1_files/sheet003.htm">

<link id="shLink">

<script language="JavaScript">
<!--
var c_lTabs=3;

var c_rgszSh=new Array(c_lTabs);
c_rgszSh[0] = "Sheet1";
c_rgszSh[1] = "Sheet2";
c_rgszSh[2] = "Sheet3";



var c_rgszClr=new Array(8);
c_rgszClr[0]="window";
c_rgszClr[1]="buttonface";
c_rgszClr[2]="windowframe";
c_rgszClr[3]="windowtext";
c_rgszClr[4]="threedlightshadow";
c_rgszClr[5]="threedhighlight";
c_rgszClr[6]="threeddarkshadow";
c_rgszClr[7]="threedshadow";

var g_iShCur;
var g_rglTabX=new Array(c_lTabs);

function fnGetIEVer()
{
var ua=window.navigator.userAgent
var msie=ua.indexOf("MSIE")
if (msie>0 && window.navigator.platform=="Win32")
return parseInt(ua.substring(msie+5,ua.indexOf(".", msie)));
else
return 0;
}

function fnBuildFrameset()
{
var szHTML="<frameset rows=\"*,18\" border=0 width=0 frameborder=no
framespacing=0>"+
"<frame src=\""+document.all.item("shLink")[0].href+"\" name=\"frSheet\"
noresize>"+
"<frameset cols=\"54,*\" border=0 width=0 frameborder=no framespacing=0>"+
"<frame src=\"\" name=\"frScroll\" marginwidth=0 marginheight=0
scrolling=no>"+
"<frame src=\"\" name=\"frTabs\" marginwidth=0 marginheight=0
scrolling=no>"+
"</frameset></frameset><plaintext>";

with (document) {
open("text/html","replace");
write(szHTML);
close();
}

fnBuildTabStrip();
}

function fnBuildTabStrip()
{
var szHTML=
"<html><head><style>.clScroll {font:8pt Courier
New;color:"+c_rgszClr[6]+";cursor:default;line-height:10pt;}"+
".clScroll2 {font:10pt
Arial;color:"+c_rgszClr[6]+";cursor:default;line-height:11pt;}</style></head>"+
"<body onclick=\"event.returnValue=false;\"
ondragstart=\"event.returnValue=false;\"
onselectstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+"
topmargin=0 leftmargin=0><table cellpadding=0 cellspacing=0 width=100%>"+
"<tr><td colspan=6 height=1 bgcolor="+c_rgszClr[2]+"></td></tr>"+
"<tr><td style=\"font:1pt\"> <td>"+
"<td valign=top id=tdScroll class=\"clScroll\"
onclick=\"parent.fnFastScrollTabs(0);\"
onmouseover=\"parent.fnMouseOverScroll(0);\"
onmouseout=\"parent.fnMouseOutScroll(0);\"><a>«</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\"
onclick=\"parent.fnScrollTabs(0);\" ondblclick=\"parent.fnScrollTabs(0);\"
onmouseover=\"parent.fnMouseOverScroll(1);\"
onmouseout=\"parent.fnMouseOutScroll(1);\"><a>&lt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll2\"
onclick=\"parent.fnScrollTabs(1);\" ondblclick=\"parent.fnScrollTabs(1);\"
onmouseover=\"parent.fnMouseOverScroll(2);\"
onmouseout=\"parent.fnMouseOutScroll(2);\"><a>&gt</a></td>"+
"<td valign=top id=tdScroll class=\"clScroll\"
onclick=\"parent.fnFastScrollTabs(1);\"
onmouseover=\"parent.fnMouseOverScroll(3);\"
onmouseout=\"parent.fnMouseOutScroll(3);\"><a>»</a></td>"+
"<td style=\"font:1pt\"> <td></tr></table></body></html>";

with (frames['frScroll'].document) {
open("text/html","replace");
write(szHTML);
close();
}

szHTML =
"<html><head>"+
"<style>A:link,A:visited,A:active
{text-decoration:none;"+"color:"+c_rgszClr[3]+";}"+
".clTab {cursor:hand;background:"+c_rgszClr[1]+";font:9pt
Arial;padding-left:3px;padding-right:3px;text-align:center;}"+
".clBorder {background:"+c_rgszClr[2]+";font:1pt;}"+
"</style></head><body onload=\"parent.fnInit();\"
onselectstart=\"event.returnValue=false;\"
ondragstart=\"event.returnValue=false;\" bgcolor="+c_rgszClr[4]+
" topmargin=0 leftmargin=0><table id=tbTabs cellpadding=0 cellspacing=0>";

var iCellCount=(c_lTabs+1)*2;

var i;
for (i=0;i<iCellCount;i+=2)
szHTML+="<col width=1><col>";

var iRow;
for (iRow=0;iRow<6;iRow++) {

szHTML+="<tr>";

if (iRow==5)
szHTML+="<td colspan="+iCellCount+"></td>";
else {
if (iRow==0) {
for(i=0;i<iCellCount;i++)
szHTML+="<td height=1 class=\"clBorder\"></td>";
} else if (iRow==1) {
for(i=0;i<c_lTabs;i++) {
szHTML+="<td height=1 nowrap class=\"clBorder\"> </td>";
szHTML+=
"<td id=tdTab height=1 nowrap class=\"clTab\"
onmouseover=\"parent.fnMouseOverTab("+i+");\"
onmouseout=\"parent.fnMouseOutTab("+i+");\">"+
"<a href=\""+document.all.item("shLink").href+"\"
target=\"frSheet\" id=aTab> "+c_rgszSh+" </a></td>";
}
szHTML+="<td id=tdTab height=1 nowrap class=\"clBorder\"><a
id=aTab> </a></td><td width=100%></td>";
} else if (iRow==2) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1></td><td height=1 class=\"clBorder\"></td>";
szHTML+="<td height=1></td><td height=1></td>";
} else if (iRow==3) {
for (i=0;i<iCellCount;i++)
szHTML+="<td height=1></td>";
} else if (iRow==4) {
for (i=0;i<c_lTabs;i++)
szHTML+="<td height=1 width=1></td><td height=1></td>";
szHTML+="<td height=1 width=1></td><td></td>";
}
}
szHTML+="</tr>";
}

szHTML+="</table></body></html>";
with (frames['frTabs'].document) {
open("text/html","replace");
charset=document.charset;
write(szHTML);
close();
}
}

function fnInit()
{
g_rglTabX[0]=0;
var i;
for (i=1;i<=c_lTabs;i++)
with (frames['frTabs'].document.all.tbTabs.rows[1].cells[fnTabToCol(i-1)])
g_rglTabX=offsetLeft+offsetWidth-6;
}

function fnTabToCol(iTab)
{
return 2*iTab+1;
}

function fnNextTab(fDir)
{
var iNextTab=-1;
var i;

with (frames['frTabs'].document.body) {
if (fDir==0) {
if (scrollLeft>0) {
for (i=0;i<c_lTabs&&g_rglTabX<scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i-1;
}
} else {
if (g_rglTabX[c_lTabs]+6>offsetWidth+scrollLeft) {
for (i=0;i<c_lTabs&&g_rglTabX<=scrollLeft;i++);
if (i<c_lTabs)
iNextTab=i;
}
}
}
return iNextTab;
}

function fnScrollTabs(fDir)
{
var iNextTab=fnNextTab(fDir);

if (iNextTab>=0) {
frames['frTabs'].scroll(g_rglTabX[iNextTab],0);
return true;
} else
return false;
}

function fnFastScrollTabs(fDir)
{
if (c_lTabs>16)
frames['frTabs'].scroll(g_rglTabX[fDir?c_lTabs-1:0],0);
else
if (fnScrollTabs(fDir)>0)
window.setTimeout("fnFastScrollTabs("+fDir+");",5);
}

function fnSetTabProps(iTab,fActive)
{
var iCol=fnTabToCol(iTab);
var i;

if (iTab>=0) {
with (frames['frTabs'].document.all) {
with (tbTabs) {
for (i=0;i<=4;i++) {
with (rows) {
if (i==0)
cells[iCol].style.background=c_rgszClr[fActive?0:2];
else if (i>0 && i<4) {
if (fActive) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[0];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
if (i==1) {
cells[iCol-1].style.background=c_rgszClr[2];
cells[iCol].style.background=c_rgszClr[1];
cells[iCol+1].style.background=c_rgszClr[2];
} else {
cells[iCol-1].style.background=c_rgszClr[4];
cells[iCol].style.background=c_rgszClr[(i==2)?2:4];
cells[iCol+1].style.background=c_rgszClr[4];
}
}
} else
cells[iCol].style.background=c_rgszClr[fActive?2:4];
}
}
}
with (aTab[iTab].style) {
cursor=(fActive?"default":"hand");
color=c_rgszClr[3];
}
}
}
}

function fnMouseOverScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[7];
}

function fnMouseOutScroll(iCtl)
{
frames['frScroll'].document.all.tdScroll[iCtl].style.color=c_rgszClr[6];
}

function fnMouseOverTab(iTab)
{
if (iTab!=g_iShCur) {
var iCol=fnTabToCol(iTab);
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[5];
}
}
}

function fnMouseOutTab(iTab)
{
if (iTab>=0) {
var elFrom=frames['frTabs'].event.srcElement;
var elTo=frames['frTabs'].event.toElement;

if ((!elTo) ||
(elFrom.tagName==elTo.tagName) ||
(elTo.tagName=="A" && elTo.parentElement!=elFrom) ||
(elFrom.tagName=="A" && elFrom.parentElement!=elTo)) {

if (iTab!=g_iShCur) {
with (frames['frTabs'].document.all) {
tdTab[iTab].style.background=c_rgszClr[1];
}
}
}
}
}

function fnSetActiveSheet(iSh)
{
if (iSh!=g_iShCur) {
fnSetTabProps(g_iShCur,false);
fnSetTabProps(iSh,true);
g_iShCur=iSh;
}
}

window.g_iIEVer=fnGetIEVer();
if (window.g_iIEVer>=4)
fnBuildFrameset();
//-->
</script>
<![endif]><!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetSource HRef="Book1_files/sheet001.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet2</x:Name>
<x:WorksheetSource HRef="Book1_files/sheet002.htm"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>Sheet3</x:Name>
<x:WorksheetSource HRef="Book1_files/sheet003.htm"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:Stylesheet HRef="Book1_files/stylesheet.css"/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>10395</x:WindowWidth>
<x:WindowTopX>240</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<frameset rows="*,39" border=0 width=0 frameborder=no framespacing=0>
<frame src="Book1_files/sheet001.htm" name="frSheet">
<frame src="Book1_files/tabstrip.htm" name="frTabs" marginwidth=0
marginheight=0>
<noframes>
<body>
<p>This page uses frames, but your browser doesn't support them.</p>
</body>
</noframes>
</frameset>
</html>

Please help! Thanks!

Did you return any data when you performed the query? You should of return
some data if you went to the Data Import External data an setup yoiu query
properly. Ive done this before with webqueries and data bases I don't have
access to a SQL server.

What you may want to due to understand the syntax better is to close the
Excel Workbook you are woring with. The open a new Excel workbook. Start
recording a macro and go to menu Data - Import External Data - New Query From
Database. Then select Excel file and the workbook you closed. You will see
the proper snytax for the dat you are looking for.

The conection property of the query can be from a file or a server. The
syntax is diferent but the SQL (command text is the same). The Table in
Access Database is the worksheet in excel. Row 1 of the Excel worksheet is
the

I'm using Excel 2003. I followed the method (Tools - Macro - Start Recording)
to record the Macro. This Macro is called Macro1. If you're talking about the
VBA macro. Here we go:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2009-6-6 by 0710058c
'

'
End Sub

Since I don't think this 'Macro1' showed much, I posted the Macro in
Microsoft Script Editor last time. Many thanks!

Wrongftype macro

From excel 2003 menu
Tools - Macro - Start Recording

or 2007
View Macros - Start Recording

The macro can be found by type Alt F11 to get to VBA and clicking on Module1
in the VBA project window



:

I went to Tool -- Microsoft Script Editor and I got below form the Macro
(called 'Macro1') and I saved it in the Personal Macro Workook, which is a
folder called 'Personal'. Thank you so much!

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=gb2312">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
<link rel=File-List href="PERSONAL_files/filelist.xml">
<link rel=Edit-Time-Data href="PERSONAL_files/editdata.mso">
<link rel=OLE-Object-Data href="PERSONAL_files/oledata.mso">
<!--[if gte mso 9]><xml>
<oocumentProperties>
<o:Author>0710058c</o:Author>
<o:LastAuthor>0710058c</o:LastAuthor>
<o:Created>2009-06-06T14:50:25Z</o:Created>
<o:LastSaved>2009-06-06T14:53:26Z</o:LastSaved>
<o:Company>UoG</o:Company>
<o:Version>11.9999</o:Version>
</oocumentProperties>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignoreadding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:134;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Sheet1</x:Name>
<x:WorksheetOptions>
<x:CodeName></x:CodeName>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHidden/>
<x:WindowHeight>8445</x:WindowHeight>
<x:WindowWidth>13275</x:WindowWidth>
<x:WindowTopX>480</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<xrotectStructure>False</xrotectStructure>
<xrotectWindows>False</xrotectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=64
style='border-collapse:
collapse;table-layout:fixed;width:48pt'>
<col width=64 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
<td height=17 width=64 style='height:12.75pt;width:48pt'></td>
</tr>
<![if supportMisalignedColumns]>
<tr height=0 style='display:none'>
<td width=64 style='width:48pt'></td>
</tr>
<![endif]>
</table>

</body>

</html>
--
Tatiana


:

Post the query you got from the recorded macro. You can filter the SQL to
get the dates within a range of dates. Perform a nd query while recording a
macro and when you get to the 2nd menu (filter) set the 1s filter to after
and put in the start date and then select a 2nd obx and use the filter before
for the 2nd date. Then post the query results. You can use a connection to
get all the obecjt between th e 2 dates.

:

Thank you so much for the advice. There is a small problem regarding changing
the format of dates in Column A though. I only can search the date 31 Jan 88
but I cannot change it within MS query. Yes, in my last post, I thought I
can change the dates. "It is feasible to open the database and retrieve the
data one row at a timeâ€. Do you know how? Thanks!
--
Tatiana


:

See below

:

Hi Joel,

I’m sorry for the confusion I caused. My data is in an Excel file called
Excelhanding.xls. The format of it were distorted after copying and pasting.
It should be:
Column A Column B Column C Column D Column E Column F
Time Debt Company Issuing date Year Arranged Debt
31 Jan 88 2.3343 A0004 30/12/2001 1988 3.0045
28 Feb 88 3.5674 A0004 30/04/2002 1989 3.0482
31 Mar 88 4.5711 A0004 30/09/2003 1990
… … A0004 01/04/2004 …
31 Dec 88 3.0045 A0004 30/09/2005 …
… … A0005 31/12/1989 …
31 Dec 89 3.0482 A0005 31/12/1990 …
31 Dec 01 3.1124 … … …
… … … … …
31 Dec 05 4.5711 … … 2001 3.1124
… … …
... … … 2005
… … … 1988
… … … …
A0005 31/12/2005 2001 … 2005
…
You’re absolutely right.! For Column A, I only have monthly data from Jan
31, 1988 to Dec 31, 2005. It is feasible to “open the database and retrieve
the data one row at a timeâ€. I just think it will be a good exercise to learn
SQL. After all, I always in a situation where I need to change Jan 31 88 to
31/12/1988 and to compare two columns.

1) I’d like to “format the data which already exists in the Excel
worksheetâ€. That said, I need to fill the Column F with the content of Column
B within this Excelhandling.xls. I’m reading two books on SQL Server 2005
Programming, a book on Excel and a book called “Integrating Excel and
Accessâ€, which also talked some basic Microsoft Visual Basic (VBA). However,
after all this reading, I’m still confused about whether I should use
Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query
(to write a program using the SQL window). I don’t think I can use Macros
though.

-------------------------------------------------------------------------------------------
You don't need a server for SQL. the SQL is really the CommandText portion
of the query. Dont learn SQL let Excel generate it for you. Start recording
a macro from the worksheet menu - tools - Macro _record New Macro. Then
perform a query by going to worksheet menu Data - Import External Data.
Choose any of the query methods.


-------------------------------------------------------------------------------------------

2) The purpose of changing the format of Column A, is to be able to compare
with Column D. Since, for example, Column A is 31 Dec 2001, I guess I have to
change it to 31/12/2001 to be recognized. This 31/12/2001 stands for the
period of 01/12/2001 to 31/12/2001. 30/12/2001 in Column D falls into this
period. Therefore 3.1124 in Column B is put the place in Column F. That is
also the reason the date does not matter. As long as Dec 01 in Column A is
correspond to 12/2001 in Column D, we can put this 3.1124 to the right place
in Column F. I realize that I can do it manually but I think it will be a
good exercise to learn SQL. After all, I always in a situation where I need
to change Jan 31 88 to 31/12/1988 and to compare two columns.

You can compare just the month and year

ColA_Date = Range("A2")
ColD_Date = Range("D2")

if month(ColA_Date) = month(ColD_Date) and _
year(ColA_Date) = year(ColD_Date) then

You can also force the date to the 1st of the month

ColA_Date = Range("A2")
FirstDay = DateSerial(year(ColA_Date), month(ColA_Date), 1)

You can also force the date to the last day of the month by going to 1st of
next month and subtracting 1 and it even works if the date is in December.
VBA thinks there are 13 months

MyDate = "12/13/09"
LastDate = DateSerial(Year(MyDate), Month(MyDate) + 1, 1) - 1






3) As for the content of Column A to D, it has already been filled with
data. I’m aware that it perhaps is too much to ask to let you write the
whole program (to fill Column F) for me, but I’m really stuck here. May I
write some codes to let you see what problem do I have from time to time,
please?